SQL Server 2012多表关联查询：按规则关联客户标记与订单

表结构 #

表Order（订单表，FinalCustomerId为Customer表外键） #

Id   FinalCustomerId  Code
1    2                 X45
2    1                 O30
3    2                 Y74
4    5                 XY0

表Customer（客户表） #

Id   Name
1    XPTO
2    BOSS
3    TREND
4    XIPS
5    VALLEY

表Article（商品表） #

Id   Name
1    DVD
2    San Disk
3    CD
4    SPAM

表OrderLine（订单行表，按OrderId和Number排序，OrderId关联Order，ArticleId关联Article） #

Id   Number   OrderId   ArticleId   
1    10       1             1
3    20       1             1
4    30       1             1
7    40       1             2

6    10       2             2
8    20       2             1

2    10       3             2 
5    20       3             2
9    10       4             4

表CustomerArticleMark（客户商品标记表，MainArticleId关联Article） #

Id   FinalCustomerId      MainArticleId   
1           2                    1
2           2                    2
3           1                    3
4           5                    2
5           3                    2
6           5                    4

需求说明 #

需为CustomerArticleMark的每一行关联包含对应商品的销售订单代码，遵循以下规则：

• 若标记商品出现在两个订单的多条订单行中，该标记需按订单数出现对应次数（而非订单行数）
• 若标记商品出现在一个订单的多条订单行中，该标记仅需出现1次
• 若标记商品未出现在任何订单行中，需保留该行并将订单代码设为空
• 每个客户、每个订单中的每个商品对应的标记需唯一

期望结果 #

Id (of CustomerArticleMark)   FinalCustomer      MainArticle     SALES ORDER Code 
 
    1                               BOSS              DVD                 X45
   
    2                               BOSS              SanDisk             Y74
    3                               XPTO              CD                  (空)      
    4                               VALLEY            SanDisk             (空)
    5                               TREND             SanDisk             (空)
    6                               VALLEY            SPAM                XY0

需返回全部6行，仅ID为3、4、5的行订单代码为空，其余行填充对应订单代码。

原SQL及问题 #

尝试的T-SQL语句：

SELECT DISTINCT  
                   {CustomerArticleMark}.[Id]
                ,  {Customer}.[Name] AS FinalCustomer
                ,  {Article}.[Name] AS MainArticle
                ,  {Order}.[Code] AS SALES ORDER Code
                
             
                FROM {CustomerArticleMark} 
                LEFT JOIN {Customer} ON {Customer}.[Id] = {CustomerArticleMark}.[FinalCustomerId] 
                LEFT JOIN {Article} ON {Article}.[Id] = {CustomerArticleMark}.[MainArticleId] 
                LEFT JOIN {SalesOrderLine} ON {SalesOrderLine}.[ArticleId] = {Article}.[Id]
                LEFT JOIN {Order} ON {Order}.[Id] = {SalesOrderLine}.[OrderId]
                WHERE {Order}.[FinalCustomerId] = {CustomerArticleMark}.[FinalCustomerId]

存在的问题：

1. 缺少ID为3、4、5的行：WHERE条件过滤掉了左连接后Order表无匹配的行（此时Order.FinalCustomerId为空，无法满足等于CustomerArticleMark.FinalCustomerId的条件）
2. 出现错误关联行：未限制客户匹配条件，导致跨客户的商品关联错误，比如ID=1的标记错误关联到SanDisk商品

修正后的SQL语句 #

SELECT 
    cam.Id,
    c.Name AS FinalCustomer,
    a.Name AS MainArticle,
    o.Code AS [SALES ORDER Code]
FROM CustomerArticleMark cam
LEFT JOIN Customer c ON c.Id = cam.FinalCustomerId
LEFT JOIN Article a ON a.Id = cam.MainArticleId
-- 预获取每个客户-订单-商品的唯一关联，避免重复行
LEFT JOIN (
    SELECT DISTINCT 
        ol.ArticleId,
        o.FinalCustomerId,
        o.Code
    FROM OrderLine ol
    JOIN [Order] o ON ol.OrderId = o.Id
) AS order_article ON 
    order_article.ArticleId = a.Id 
    AND order_article.FinalCustomerId = cam.FinalCustomerId
ORDER BY cam.Id;

修正说明 #

1. 子查询去重：通过子查询order_article先对OrderLine和Order关联并去重，确保每个FinalCustomerId-ArticleId-Order.Code组合唯一，避免同一订单多条商品行导致的重复结果
2. 客户匹配移至JOIN条件：将客户匹配逻辑放在LEFT JOIN的ON子句中，而非WHERE子句，保证CustomerArticleMark的所有行都能被返回
3. 精准关联：同时匹配商品ID和客户ID，彻底避免跨客户的错误关联

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