多组一对多关系下SQL复杂JOIN查询实现方案

实现思路 #

原实现的核心逻辑方向正确，问题在于硬编码了最大5个关联项的上限，且使用全局最大序号判断容易出现跨订单序号错位。优化方案通过CTE完成全流程处理，无硬编码数量限制，完全避免两个一对多表关联产生的笛卡尔积：

• 按订单维度分区，分别给商品、备注生成从1开始的连续行号
• 动态生成覆盖全量数据最大行号的连续序号序列，无需提前定义支持的最大关联项数
• 按订单号+行号分别关联商品、备注，无匹配项自动填充NULL，过滤商品和备注均为空的无效行

可直接运行的优化代码 #

WITH OrderItems AS (
    SELECT
        ER101_ORD_NBR,
        ER101_DESC,
        -- 按订单分区为商品生成连续行号，排序字段可根据业务规则调整
        ROW_NUMBER() OVER (PARTITION BY ER101_ORG_CODE, ER101_EVT_ID, ER101_ORD_NBR ORDER BY ER101_DESC) AS RowRank
    FROM ER101_ACCT_ORDER_DTL
),
OrderNotes AS (
    SELECT
        CC025_ORDER,
        CC025_NOTE_TEXT,
        -- 按订单分区为备注生成连续行号，排序字段可根据业务规则调整
        ROW_NUMBER() OVER (PARTITION BY CC025_ORDER ORDER BY CC025_NOTE_TEXT) AS RowRank
    FROM CC025_NOTES_EXT
),
-- 统计商品、备注的全局最大行号
MaxRankValues AS (
    SELECT MAX(RowRank) AS MaxRank FROM OrderItems
    UNION ALL
    SELECT MAX(RowRank) AS MaxRank FROM OrderNotes
),
-- 递归生成从1到最大行号的连续序号序列
RankSeq AS (
    SELECT 1 AS RowRank
    UNION ALL
    SELECT RowRank + 1 FROM RankSeq
    WHERE RowRank < (SELECT MAX(MaxRank) FROM MaxRankValues)
)
SELECT
    o.ER100_ORD_NBR AS OrderNumber,
    oi.ER101_DESC AS Item,
    nt.CC025_NOTE_TEXT AS Note
FROM ER100_ACCT_ORDER o
CROSS JOIN RankSeq seq
LEFT JOIN OrderItems oi
    ON o.ER100_ORD_NBR = oi.ER101_ORD_NBR
    AND seq.RowRank = oi.RowRank
LEFT JOIN OrderNotes nt
    ON o.ER100_ORD_NBR = nt.CC025_ORDER
    AND seq.RowRank = nt.RowRank
-- 剔除商品、备注均为空的无效占位行
WHERE oi.ER101_DESC IS NOT NULL OR nt.CC025_NOTE_TEXT IS NOT NULL
ORDER BY o.ER100_ORD_NBR, seq.RowRank

说明 #

• 代码中使用ROW_NUMBER()替代原实现的RANK()，避免相同排序值生成重复行号导致数据错位；如果业务需要相同值共用同一序号，可替换回RANK()，注意重复序号会产生对应空行
• 递归生成的序号序列会自动适配当前数据的最大关联项数量，无硬编码上限，支持任意数量的商品、备注关联
• 若使用SQL Server 2022及以上版本，可将递归生成RankSeq的部分替换为GENERATE_SERIES(1, (SELECT MAX(MaxRank) FROM MaxRankValues))，执行效率更高

示例数据返回结果 #

按照给出的测试数据，执行代码后返回结果如下，完全符合对齐要求：

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