SQL Server透视表按站点分组取最小到期日期的实现问题

我查阅了一些类似问题，但仍未找到解决方案。我希望每个站点仅显示一行数据，展示该站点所有项的最小到期日期，比如站点3的预期结果如下：

SiteID  | DateDue    |  A   |   B   |   C   |   D
--------+------------+------+-------+-------+-----
   3    | 2023-08-11 |  2   |   0   |   0   |   1

原查询语句 #

SELECT * 
FROM  
    (SELECT 
         Sites.ID AS SiteID, 
         MIN(DateInspectionDue) AS DateDue, 
         ItemType, SiteItems.ID AS SiteItemID
     FROM 
         Clients
     INNER JOIN 
         Sites ON Clients.ID = Sites.ClientID
     INNER JOIN 
         SiteItems ON Sites.ID = SiteItems.SiteID
     INNER JOIN 
         Items ON SiteItems.ItemID = Items.ID
     GROUP BY 
         Sites.ID, ItemType, SiteItems.ID
     HAVING 
         MIN(SiteItems.DateInspectionDue) < '2023-09-01') t
PIVOT
    (COUNT(SiteItemID)
     FOR ItemType IN (A, B, C, D)
    ) pivot_table
GROUP BY 
    DateDue, SiteID, A, B, C, D
ORDER BY 
    SiteID

相关数据说明 #

• 示例数据：包含Clients、Sites、SiteItems、Items四个表，记录了客户、站点、站点关联物品的类型、到期日期等信息
• 当前查询结果：每个站点会因不同到期日期显示多行，不符合需求

解决方案 #

原查询的分组逻辑导致同一站点出现多行结果，需要调整步骤：先获取站点的最小到期日期，再统计各物品类型的数量，最后合并结果。修改后的SQL语句如下：

WITH SiteItemData AS (
    SELECT 
        s.ID AS SiteID,
        si.DateInspectionDue,
        i.ItemType,
        si.ID AS SiteItemID
    FROM Clients c
    INNER JOIN Sites s ON c.ID = s.ClientID
    INNER JOIN SiteItems si ON s.ID = si.SiteID
    INNER JOIN Items i ON si.ItemID = i.ID
    WHERE si.DateInspectionDue < '2023-09-01'
),
SiteMinDue AS (
    SELECT 
        SiteID,
        MIN(DateInspectionDue) AS DateDue
    FROM SiteItemData
    GROUP BY SiteID
)
SELECT 
    smd.SiteID,
    smd.DateDue,
    ISNULL(pvt.A, 0) AS A,
    ISNULL(pvt.B, 0) AS B,
    ISNULL(pvt.C, 0) AS C,
    ISNULL(pvt.D, 0) AS D
FROM SiteMinDue smd
LEFT JOIN (
    SELECT 
        SiteID,
        COUNT(CASE WHEN ItemType = 'A' THEN SiteItemID END) AS A,
        COUNT(CASE WHEN ItemType = 'B' THEN SiteItemID END) AS B,
        COUNT(CASE WHEN ItemType = 'C' THEN SiteItemID END) AS C,
        COUNT(CASE WHEN ItemType = 'D' THEN SiteItemID END) AS D
    FROM SiteItemData
    GROUP BY SiteID
) pvt ON smd.SiteID = pvt.SiteID
ORDER BY smd.SiteID;

逻辑说明 #

1. SiteItemData：先筛选出所有到期日期早于2023-09-01的记录，减少后续分组的重复计算
2. SiteMinDue：单独计算每个站点的最小到期日期，确保每个站点仅返回一个值
3. 最后通过左连接将站点的最小到期日期与各类型物品统计数结合，用ISNULL处理无对应类型的情况，默认显示为0

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