如何向MySQL子查询传递父查询当前行的OD.ID参数值？

该操作完全可行，属于SQL标准的相关子查询能力，你当前代码无法获取外层ID的核心原因是子查询内的OrderDetail表别名和外层表别名OD重名，按照SQL作用域内层优先的规则，子查询内的OD.ID会优先匹配子查询自身的表字段，无法引用外层当前记录的ID值。

修复规则 #

• 子查询内的OrderDetail必须使用和外层不同的别名，例如OD_sub
• 关联条件直接写子查询表别名.ID = 外层表别名.ID即可，SQL会自动逐行带入外层当前记录的ID值执行统计，不需要额外传参
• 子查询统计去重下单天数时，因为已经按外层ID做了过滤，只需要统计DISTINCT 购买日期即可，不需要把ID放入distinct字段
• 增加除零保护，避免某个用户没有下单记录时触发除以0的SQL错误

修正后代码 #

public static function getAthleteReportByDates($start_date, $end_date){
    return Database::runQuery(
        "SELECT 
        OD.ID,
        IF(ISNULL(A.FuelID)=1, 'No Fuel ID', A.FuelID) AS FuelID,
        IF(ISNULL(CONCAT(FirstName, ' ', LastName))=1, 'N/A', CONCAT(FirstName, ' ', LastName)) As Name,
        IF(ISNULL(A.Gender)=1, 'N/A', A.Gender) AS Gender,
        IF(ISNULL(S.Name)=1, 'N/A', S.Name) As Sport,
        SUM(F.Price) As Total,
        IFNULL(
            SUM(F.Price) / (
                SELECT COUNT(DISTINCT OD_sub.Day) 
                FROM OrderDetail OD_sub 
                WHERE OD_sub.Day BETWEEN :start_date AND :end_date 
                  AND OD_sub.ID = OD.ID
            ),
        0) AS Average                              
        FROM OrderDetail OD
        LEFT JOIN Food F
        ON OD.FoodID = F.ID
        LEFT JOIN Athlete A
        ON A.ID = OD.ID
        LEFT JOIN PlaysFor PR
        ON PR.ID = OD.ID
        LEFT JOIN Sports S
        ON PR.SPORT_ID = S.ID
        WHERE OD.Day BETWEEN :start_date AND :end_date
        GROUP BY OD.ID, Sport", 
        array(
            'start_date' => $start_date,
            'end_date'=> $end_date
        ));
}

性能优化可选方案 #

如果OrderDetail表数据量较大，逐行执行相关子查询的性能较差，可以提前聚合出所有符合日期范围的用户对应去重下单天数，再通过JOIN关联到主查询，逻辑示例：

-- 提前聚合每个用户的去重下单天数
WITH UserOrderDays AS (
  SELECT ID, COUNT(DISTINCT Day) AS order_days
  FROM OrderDetail
  WHERE Day BETWEEN :start_date AND :end_date
  GROUP BY ID
)
-- 主查询直接关联聚合结果计算均值
SELECT 
  OD.ID,
  -- 其余查询字段和原逻辑一致
  SUM(F.Price) AS Total,
  IFNULL(SUM(F.Price)/UOD.order_days, 0) AS Average
FROM OrderDetail OD
LEFT JOIN UserOrderDays UOD ON OD.ID = UOD.ID
-- 其余JOIN、WHERE、GROUP BY逻辑和原逻辑一致

内容的提问来源于stack exchange，提问作者btpaul12