MySQL关联子查询获取匹配首行 最小行号问题求解

• 方案1失效原因：子查询内的ROW_NUMBER()是针对每个商品的全量入库记录做全局排序编号，没有结合主表的日期条件过滤，row_num=1永远指向每个商品全局最新的入库记录，外层再写日期过滤时，要么匹配到这条全局最新记录（如果它满足日期要求），要么匹配不到任何结果，完全无法拿到每个主表行对应日期节点下的目标记录。尝试替换为E.row_num = MIN(E.row_num)也无法解决问题，聚合逻辑会破坏行级关联关系，无法正确返回对应字段。
• 方案2失效原因：JOIN后跟随的普通派生表会优先于主查询执行，执行阶段无法读取外层主表gsm_solde.Date的值，会直接抛出字段不存在的报错。只有相关子查询、支持逐行关联的APPLY/LATERAL语法才能在子查询内引用外层主表字段。

注：你提供的预期结果中，关联的入库记录日期均晚于对应主表记录的日期，和你写的E.DateE <= gsm_solde.Date条件逻辑相反，以下解法先按照你给出的样例预期（取主表日期之后最近的一条入库记录）编写，如果实际需求是取主表日期及之前的最新入库，只需要调整日期比较符和排序方向即可，代码中会做标注。

方案1：兼容性最好的窗口函数写法（支持所有含窗口函数的数据库：MySQL8+、PostgreSQL、SQL Server、Oracle等） #

核心逻辑是先把主表和入库表按商品ID、日期范围做关联，再针对每个主表行的匹配结果排序取第一条，完全避免全局编号的问题：

SELECT 
    id,
    DATES,
    IdArticle,
    DateE,
    QteE
FROM (
    SELECT 
         gsm_solde.id AS id
        , gsm_solde.date AS DATES
        , gsm_solde.IdArticle AS IdArticle
        , gds_entree.date AS DateE
        , gds_entree_detail.qte_new AS QteE
        , ROW_NUMBER() OVER (
            PARTITION BY gsm_solde.id 
            -- 找主表日期之后最近的记录用ASC排序，找之前最新的记录改成DESC
            ORDER BY gds_entree.date ASC, gds_entree_detail.id ASC
        ) AS row_num
    FROM gsm_solde
    LEFT JOIN gds_entree_detail 
        ON gsm_solde.IdArticle = gds_entree_detail.id_stk
    LEFT JOIN gds_entree 
        ON gds_entree_detail.IdMouvement = gds_entree.id
        -- 找之后的记录用>，找之前/当天的记录改成<=
        AND gds_entree.date > gsm_solde.Date
    WHERE gsm_solde.date BETWEEN 20220619000000 AND 20220619235959
) t
WHERE row_num = 1

方案2：逐行关联写法（逻辑更直观） #

该写法会逐行遍历主表记录，将主表字段传入子查询过滤、排序后取第一条，完全贴合“首行规则依赖主表结果”的需求，不同数据库语法略有区别：

-- SQL Server版本
SELECT 
    gsm_solde.id AS id
    , gsm_solde.date AS DATES
    , gsm_solde.IdArticle AS IdArticle
    , E.DateE
    , E.QteE
FROM gsm_solde
-- 无匹配记录需要返回空用OUTER APPLY，确定一定有匹配用CROSS APPLY
OUTER APPLY (
    SELECT TOP 1
         gds_entree_detail.qte_new AS QteE
        , gds_entree.date AS DateE
    FROM gds_entree 
    INNER JOIN gds_entree_detail 
        ON gds_entree.id = gds_entree_detail.IdMouvement
    WHERE gds_entree_detail.id_stk = gsm_solde.IdArticle
        -- 日期条件同前，按需求调整比较符
        AND gds_entree.date > gsm_solde.Date
    -- 排序方向同前，按需求调整ASC/DESC
    ORDER BY gds_entree.date ASC, gds_entree_detail.id ASC
) E
WHERE gsm_solde.date BETWEEN 20220619000000 AND 20220619235959

-- MySQL8.0.14+ / PostgreSQL版本
SELECT 
    gsm_solde.id AS id
    , gsm_solde.date AS DATES
    , gsm_solde.IdArticle AS IdArticle
    , E.DateE
    , E.QteE
FROM gsm_solde
LEFT JOIN LATERAL (
    SELECT 
         gds_entree_detail.qte_new AS QteE
        , gds_entree.date AS DateE
    FROM gds_entree 
    INNER JOIN gds_entree_detail 
        ON gds_entree.id = gds_entree_detail.IdMouvement
    WHERE gds_entree_detail.id_stk = gsm_solde.IdArticle
        AND gds_entree.date > gsm_solde.Date
    ORDER BY gds_entree.date ASC, gds_entree_detail.id ASC
    LIMIT 1
) E ON true
WHERE gsm_solde.date BETWEEN 20220619000000 AND 20220619235959

以上写法跑你提供的样例数据，会完全返回你给出的预期结果。你原SQL里多余的GROUP BY gsm_solde.id已经移除——如果gsm_solde.id是主键，本身行就是唯一的，不需要分组，保留这个写法在ONLY_FULL_GROUP_BY模式下还会触发字段非聚合的报错。

内容的提问来源于stack exchange，提问作者Mohamed Tahar Akel