如何在子查询中引用父查询的id_product字段？

问题原因 #

你遇到的是SQL子查询的作用域限制问题：多层嵌套的子查询中，最内层查询无法直接引用最外层父查询的字段，只能访问直接外层查询的变量，因此sh.id = p.id_product会失效。

解决方案 #

以下两种方法可实现对每个id_product执行子查询的需求：

方法1：使用LATERAL JOIN（适用于MySQL 8.0+、PostgreSQL等支持的数据库）

这种方式允许内层查询直接引用外层表字段，每个id_product会独立执行一次内层查询：

SELECT 
    p.id_product,     
    COALESCE(SUM(sub.total_quantity), 0) AS AVG30
FROM
    ps1769_product p
LEFT JOIN LATERAL (
    SELECT SUM(cp.quantity) AS total_quantity
    FROM ps1769_xcustom_stock_history sh
    LEFT JOIN ps1769_cart_product cp 
        ON cp.id_product = sh.id AND DATE(cp.date_add) = sh.date
    WHERE sh.id = p.id_product
      AND sh.date <= '2023-10-10'
      AND sh.quantity > 0
    GROUP BY sh.date
    ORDER BY sh.date DESC
    LIMIT 30
) AS sub ON TRUE
GROUP BY p.id_product;

方法2：窗口函数筛选前30条记录（兼容更多数据库版本）

先通过窗口函数给每个id的日期按倒序排名，筛选出最近30天，再关联计算总和：

SELECT 
    p.id_product,
    COALESCE(SUM(cp_sum.total_quantity), 0) AS AVG30
FROM ps1769_product p
LEFT JOIN (
    SELECT 
        sh.id,
        sh.date,
        SUM(cp.quantity) AS total_quantity
    FROM ps1769_xcustom_stock_history sh
    LEFT JOIN ps1769_cart_product cp 
        ON cp.id_product = sh.id AND DATE(cp.date_add) = sh.date
    WHERE sh.date <= '2023-10-10'
      AND sh.quantity > 0
    GROUP BY sh.id, sh.date
) AS cp_sum ON cp_sum.id = p.id_product
INNER JOIN (
    SELECT id, date
    FROM (
        SELECT 
            id,
            date,
            ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS rn
        FROM ps1769_xcustom_stock_history
        WHERE date <= '2023-10-10'
          AND quantity > 0
    ) AS ranked
    WHERE rn <= 30
) AS top30_dates ON top30_dates.id = cp_sum.id AND top30_dates.date = cp_sum.date
GROUP BY p.id_product;

内容的提问来源于stack exchange，提问作者Daniel Krzyżanowski