MYSQL多次JOIN同一表按多条件提取最新月度销量数据问题求助

• 原查询中子查询SELECT max(id) FROM ahlaan_forecast_monthly_sales GROUP BY Month_Year仅按月份分组，每个月只会返回全局最大ID的1条预测记录，因此只有对应SSKU能匹配到数据，其余SSKU的关联字段全部返回null。
• 原有逻辑没有按照需求实现「每个SSKU+对应月份取最大Version最新版本」的规则，错误使用了max(id)作为判断依据，不符合业务要求。

你可以保留原有6次LEFT JOIN的结构，仅修改每个月的子查询逻辑，让每个子查询返回对应月份下每个SSKU的最新版本预测数据即可，修改后完整SQL如下：

SELECT 
ssku.`id` as SSKUID, 
ssku.`SSKU`, 
ssku.`Description`,
salesForecastSep.Qty as Sep_2021_qty, salesForecastSep.Version as Sep2021_Version,
salesForecastOct.Qty as Oct_2021_qty, salesForecastOct.Version as Oct2021_Version,
salesForecastNov.Qty as Nov_2021_qty, salesForecastNov.Version as Nov2021_Version,
salesForecastDec.Qty as Dec_2021_qty, salesForecastDec.Version as Dec2021_Version,
salesForecastJan.Qty as Jan_2022_qty, salesForecastJan.Version as Jan2022_Version,
salesForecastFeb.Qty as Feb_2022_qty, salesForecastFeb.Version as Feb2022_Version
FROM `ahlaan_ssku` ssku 
LEFT JOIN ( 
    SELECT f.* FROM `ahlaan_forecast_monthly_sales` f
    INNER JOIN (
        SELECT SSKUID, MAX(Version) AS max_ver FROM `ahlaan_forecast_monthly_sales` 
        WHERE Month_Year = '2021-09-01'
        GROUP BY SSKUID
    ) t ON f.SSKUID = t.SSKUID AND f.Version = t.max_ver
    WHERE f.Month_Year = '2021-09-01'
) AS salesForecastSep ON ssku.id = salesForecastSep.SSKUID
LEFT JOIN ( 
    SELECT f.* FROM `ahlaan_forecast_monthly_sales` f
    INNER JOIN (
        SELECT SSKUID, MAX(Version) AS max_ver FROM `ahlaan_forecast_monthly_sales` 
        WHERE Month_Year = '2021-10-01'
        GROUP BY SSKUID
    ) t ON f.SSKUID = t.SSKUID AND f.Version = t.max_ver
    WHERE f.Month_Year = '2021-10-01'
) AS salesForecastOct ON ssku.id = salesForecastOct.SSKUID
LEFT JOIN ( 
    SELECT f.* FROM `ahlaan_forecast_monthly_sales` f
    INNER JOIN (
        SELECT SSKUID, MAX(Version) AS max_ver FROM `ahlaan_forecast_monthly_sales` 
        WHERE Month_Year = '2021-11-01'
        GROUP BY SSKUID
    ) t ON f.SSKUID = t.SSKUID AND f.Version = t.max_ver
    WHERE f.Month_Year = '2021-11-01'
) AS salesForecastNov ON ssku.id = salesForecastNov.SSKUID
LEFT JOIN ( 
    SELECT f.* FROM `ahlaan_forecast_monthly_sales` f
    INNER JOIN (
        SELECT SSKUID, MAX(Version) AS max_ver FROM `ahlaan_forecast_monthly_sales` 
        WHERE Month_Year = '2021-12-01'
        GROUP BY SSKUID
    ) t ON f.SSKUID = t.SSKUID AND f.Version = t.max_ver
    WHERE f.Month_Year = '2021-12-01'
) AS salesForecastDec ON ssku.id = salesForecastDec.SSKUID
LEFT JOIN ( 
    SELECT f.* FROM `ahlaan_forecast_monthly_sales` f
    INNER JOIN (
        SELECT SSKUID, MAX(Version) AS max_ver FROM `ahlaan_forecast_monthly_sales` 
        WHERE Month_Year = '2022-01-01'
        GROUP BY SSKUID
    ) t ON f.SSKUID = t.SSKUID AND f.Version = t.max_ver
    WHERE f.Month_Year = '2022-01-01'
) AS salesForecastJan ON ssku.id = salesForecastJan.SSKUID
LEFT JOIN ( 
    SELECT f.* FROM `ahlaan_forecast_monthly_sales` f
    INNER JOIN (
        SELECT SSKUID, MAX(Version) AS max_ver FROM `ahlaan_forecast_monthly_sales` 
        WHERE Month_Year = '2022-02-01'
        GROUP BY SSKUID
    ) t ON f.SSKUID = t.SSKUID AND f.Version = t.max_ver
    WHERE f.Month_Year = '2022-02-01'
) AS salesForecastFeb ON ssku.id = salesForecastFeb.SSKUID
WHERE ssku.ActiveStatus = '1' AND `SalesManagerID` = 302

注：顺带修正了你原查询中Jan和Feb的字段别名错误，原别名写了2021，实际月份是2022年的。

如果你的数据库支持窗口函数，可以简化写法，不用写6次重复的子查询：

WITH forecast_latest AS (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY SSKUID, Month_Year ORDER BY Version DESC) as rn
    FROM ahlaan_forecast_monthly_sales
    WHERE Month_Year IN ('2021-09-01','2021-10-01','2021-11-01','2021-12-01','2022-01-01','2022-02-01')
)
SELECT 
ssku.id as SSKUID,
ssku.SSKU,
ssku.Description,
MAX(IF(Month_Year='2021-09-01',Qty,NULL)) as Sep_2021_qty,
MAX(IF(Month_Year='2021-09-01',Version,NULL)) as Sep2021_Version,
MAX(IF(Month_Year='2021-10-01',Qty,NULL)) as Oct_2021_qty,
MAX(IF(Month_Year='2021-10-01',Version,NULL)) as Oct2021_Version,
MAX(IF(Month_Year='2021-11-01',Qty,NULL)) as Nov_2021_qty,
MAX(IF(Month_Year='2021-11-01',Version,NULL)) as Nov2021_Version,
MAX(IF(Month_Year='2021-12-01',Qty,NULL)) as Dec_2021_qty,
MAX(IF(Month_Year='2021-12-01',Version,NULL)) as Dec2021_Version,
MAX(IF(Month_Year='2022-01-01',Qty,NULL)) as Jan_2022_qty,
MAX(IF(Month_Year='2022-01-01',Version,NULL)) as Jan2022_Version,
MAX(IF(Month_Year='2022-02-01',Qty,NULL)) as Feb_2022_qty,
MAX(IF(Month_Year='2022-02-01',Version,NULL)) as Feb2022_Version
FROM ahlaan_ssku ssku
LEFT JOIN forecast_latest f ON ssku.id = f.SSKUID AND f.rn = 1
WHERE ssku.ActiveStatus = 1 AND ssku.SalesManagerID = 302
GROUP BY ssku.id, ssku.SKU, ssku.Description

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