MSSQL2005如何实现商品价目表关联后无重复的紧凑结果输出

兼容MSSQL 2005的实现方案 #

你遇到的多表关联冗余是因为同商品下N条价目A记录和M条价目B记录直接关联会产生N*M的笛卡尔积，解决核心是给每个价目表的同商品记录分配独立行号，再用商品ID+行号作为关联条件，就能得到无冗余的紧凑对齐结果。

实现逻辑

1. 给每一张价目表的同商品记录按自定义规则（比如生效日期、价格高低）分配行号
2. 合并所有价目表的商品ID+行号组合，避免某张价目表行数更多时漏行
3. 用商品ID+行号关联所有价目表，无匹配的价目字段自动留空

示例代码

假设商品主表为Product（字段：ProductID商品ID、ProductName商品名称），两张价目表分别为PriceList_A、PriceList_B，示例SQL如下：

WITH
-- 对价目表A的同商品记录按生效日期倒序分配行号
PLA AS (
    SELECT 
        ProductID, PriceA, EffectiveDateA,
        ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY EffectiveDateA DESC) AS rn
    FROM PriceList_A
),
-- 对价目表B的同商品记录按生效日期倒序分配行号
PLB AS (
    SELECT 
        ProductID, PriceB, EffectiveDateB,
        ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY EffectiveDateB DESC) AS rn
    FROM PriceList_B
),
-- 合并所有商品+行号组合，避免行数少的价目表导致漏行
AllLineNo AS (
    SELECT ProductID, rn FROM PLA
    UNION
    SELECT ProductID, rn FROM PLB
)
SELECT 
    p.ProductID, p.ProductName,
    pla.PriceA, pla.EffectiveDateA,
    plb.PriceB, plb.EffectiveDateB
FROM Product p
INNER JOIN AllLineNo aln ON p.ProductID = aln.ProductID
LEFT JOIN PLA pla ON p.ProductID = pla.ProductID AND aln.rn = pla.rn
LEFT JOIN PLB plb ON p.ProductID = plb.ProductID AND aln.rn = plb.rn
ORDER BY p.ProductID, aln.rn

适配说明

• 该方案完全兼容MSSQL 2005，用到的ROW_NUMBER()窗口函数、CTE语法均为2005版本原生支持
• 如果有更多价目表，只需按照相同逻辑新增对应的行号分配CTE、在AllLineNo中补充UNION、最后新增LEFT JOIN关联即可
• 行号的排序规则可根据业务需求调整，比如按价格高低、录入时间排序只需修改OVER子句中ORDER BY后的字段即可

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