SQL Server行合并问题：Timestamp导致无法汇总InvoiceQty的解决方案咨询

核心问题是你在did子查询的GROUP BY中包含了CreateDate，导致相同**容器编号（CustomerLotReference）和SKU（WarehouseSku）**的行，只要CreateDate不同就会被分成独立分组，无法合并求和InvoicedQty。

你需要对CreateDate做聚合处理，而不是直接将其放入GROUP BY，以下是几种常见的处理方案：

方案1：只保留日期部分（去除时分秒）并分组 #

如果只需要日期维度（不需要具体时间），可以把CreateDate转换为日期类型后再分组：

WITH did AS
( 
    SELECT
        [did].[CustomerLotReference] AS Container,
        [did].[WarehouseSku] AS warehouessku,
        SUM([did].[InvoicedQty]) AS invoiceqty,
        CONVERT(date, [did].[CreateDate]) AS CreateDate -- 转换为日期类型，去掉时分秒
    FROM 
        [server].[dbo].[table] AS did
    WHERE 
        [did].[table] = 'customer'
    GROUP BY 
        [did].[CustomerLotReference],
        [did].[WarehouseSku],
        CONVERT(date, [did].[CreateDate]) -- 按转换后的日期分组
), so AS 
(
    SELECT
        [so].[OrderGroup] AS container,
        [so].OtherReferenceFour AS warehousesku,
        SUM([so].[QtyOrdered]) AS AllocatedQty
    FROM
        [server].[dbo].[table] AS so
    GROUP BY
        [so].[OrderGroup], [so].OtherReferenceFour
)
SELECT
    did.*, so.*
FROM
    did
LEFT JOIN
    so ON so.container = did.Container 
       AND so.warehousesku = did.warehouessku

方案2：取该容器+SKU对应的最早创建日期 #

如果需要保留该组下最早的创建时间：

WITH did AS
( 
    SELECT
        [did].[CustomerLotReference] AS Container,
        [did].[WarehouseSku] AS warehouessku,
        SUM([did].[InvoicedQty]) AS invoiceqty,
        MIN([did].[CreateDate]) AS CreateDate -- 取最早的创建时间
    FROM 
        [server].[dbo].[table] AS did
    WHERE 
        [did].[table] = 'customer'
    GROUP BY 
        [did].[CustomerLotReference],
        [did].[WarehouseSku] -- 不再按CreateDate分组
), so AS 
(
    SELECT
        [so].[OrderGroup] AS container,
        [so].OtherReferenceFour AS warehousesku,
        SUM([so].[QtyOrdered]) AS AllocatedQty
    FROM
        [server].[dbo].[table] AS so
    GROUP BY
        [so].[OrderGroup], [so].OtherReferenceFour
)
SELECT
    did.*, so.*
FROM
    did
LEFT JOIN
    so ON so.container = did.Container 
       AND so.warehousesku = did.warehouessku

方案3：取该容器+SKU对应的最晚创建日期 #

如果需要保留该组下最新的创建时间：

WITH did AS
( 
    SELECT
        [did].[CustomerLotReference] AS Container,
        [did].[WarehouseSku] AS warehouessku,
        SUM([did].[InvoicedQty]) AS invoiceqty,
        MAX([did].[CreateDate]) AS CreateDate -- 取最晚的创建时间
    FROM 
        [server].[dbo].[table] AS did
    WHERE 
        [did].[table] = 'customer'
    GROUP BY 
        [did].[CustomerLotReference],
        [did].[WarehouseSku] -- 不再按CreateDate分组
), so AS 
(
    SELECT
        [so].[OrderGroup] AS container,
        [so].OtherReferenceFour AS warehousesku,
        SUM([so].[QtyOrdered]) AS AllocatedQty
    FROM
        [server].[dbo].[table] AS so
    GROUP BY
        [so].[OrderGroup], [so].OtherReferenceFour
)
SELECT
    did.*, so.*
FROM
    did
LEFT JOIN
    so ON so.container = did.Container 
       AND so.warehousesku = did.warehouessku

方案4：不需要CreateDate字段时直接移除 #

如果查询结果不需要CreateDate，直接从SELECT和GROUP BY中去掉即可：

WITH did AS
( 
    SELECT
        [did].[CustomerLotReference] AS Container,
        [did].[WarehouseSku] AS warehouessku,
        SUM([did].[InvoicedQty]) AS invoiceqty
    FROM 
        [server].[dbo].[table] AS did
    WHERE 
        [did].[table] = 'customer'
    GROUP BY 
        [did].[CustomerLotReference],
        [did].[WarehouseSku]
), so AS 
(
    SELECT
        [so].[OrderGroup] AS container,
        [so].OtherReferenceFour AS warehousesku,
        SUM([so].[QtyOrdered]) AS AllocatedQty
    FROM
        [server].[dbo].[table] AS so
    GROUP BY
        [so].[OrderGroup], [so].OtherReferenceFour
)
SELECT
    did.*, so.*
FROM
    did
LEFT JOIN
    so ON so.container = did.Container 
       AND so.warehousesku = did.warehouessku

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