SQL向DECLARE声明的表变量插入数据时Employee ID重复如何解决

两次独立INSERT操作会导致同时有销售和退货记录的员工生成两条重复数据，此外第一次插入语句存在笔误：第三列别名错误声明为ChargedQty，实际应为ReturnQty，同时退货查询的表关联条件存在错误，已在下方方案中修正。

方案1：一次性插入聚合结果（推荐） #

将两个业务查询的结果通过UNION ALL合并后，按员工ID聚合求和，一次性写入表变量，从根源避免重复：

DECLARE @WSotable TABLE 
                  (
                      Employee int, 
                      ChargedQty int,
                      ReturnQty int
                  )

INSERT INTO @WSotable
SELECT 
    EmployeeID,
    SUM(ChargedQty) AS ChargedQty,
    SUM(ReturnQty) AS ReturnQty
FROM (
    -- 第一个查询取销售数据
    SELECT
        MD_Employee.ID AS EmployeeID,
        SUM(TR_InvoiceDetails.InvoiceQuantity) AS ChargedQty,
        0 AS ReturnQty
    FROM
        TR_SalesOrderHeader 
    INNER JOIN 
        TR_InvoiceHeader ON TR_InvoiceHeader.SalesOrderID  = TR_SalesOrderHeader.ID 
    INNER JOIN 
        TR_InvoiceDetails ON TR_InvoiceDetails.InvoiceID = TR_InvoiceHeader.ID
    INNER JOIN 
        MD_ItemMaster ON TR_InvoiceDetails.ItemID  =  MD_ItemMaster.ID
    INNER JOIN 
        MD_Supplier ON MD_ItemMaster.SupplierID= MD_Supplier.ID
    INNER JOIN 
        MD_Employee ON TR_SalesOrderHeader.CreatedBy = MD_Employee.ID
    WHERE
        MD_Supplier.ID = 3 
        AND MD_ItemMaster.ID = 572
    GROUP BY
        MD_Employee.ID

    UNION ALL

    -- 第二个查询取退货数据
    SELECT
        MD_Employee.ID AS EmployeeID,
        0 AS ChargedQty,
        SUM(TR_SalesReturnDetails.ReturnQuantity) AS ReturnQty
    FROM
        TR_SalesReturnHeader
    INNER JOIN
        TR_SalesReturnDetails ON TR_SalesReturnDetails.SalesReturnID = TR_SalesReturnHeader.ID
    INNER JOIN 
        MD_ItemMaster ON TR_SalesReturnDetails.ItemID  =  MD_ItemMaster.ID
    INNER JOIN 
        MD_Supplier ON MD_ItemMaster.SupplierID= MD_Supplier.ID
    INNER JOIN 
        MD_Employee ON TR_SalesReturnHeader.CreatedBy = MD_Employee.ID
    WHERE
        MD_Supplier.ID = 3 
        AND MD_ItemMaster.ID = 572
    GROUP BY
        MD_Employee.ID
) AS t
GROUP BY EmployeeID

方案2：分两次操作，用MERGE避免重复 #

如果业务逻辑要求必须分两次写入，可以在第二次写入时用MERGE语句，匹配到已存在的员工ID就更新退货量，匹配不到再新增：

-- 先插入销售数据
INSERT INTO @WSotable
SELECT
    MD_Employee.ID,
    SUM(TR_InvoiceDetails.InvoiceQuantity) AS ChargedQty,
    0 AS ReturnQty
FROM
    TR_SalesOrderHeader 
INNER JOIN 
    TR_InvoiceHeader ON TR_InvoiceHeader.SalesOrderID  = TR_SalesOrderHeader.ID 
INNER JOIN 
    TR_InvoiceDetails ON TR_InvoiceDetails.InvoiceID = TR_InvoiceHeader.ID
INNER JOIN 
    MD_ItemMaster ON TR_InvoiceDetails.ItemID  =  MD_ItemMaster.ID
INNER JOIN 
    MD_Supplier ON MD_ItemMaster.SupplierID= MD_Supplier.ID
INNER JOIN 
    MD_Employee ON TR_SalesOrderHeader.CreatedBy = MD_Employee.ID
WHERE
    MD_Supplier.ID = 3 
    AND MD_ItemMaster.ID = 572
GROUP BY
    MD_Employee.ID

-- 第二次用MERGE处理退货数据
MERGE INTO @WSotable AS target
USING (
    SELECT
        MD_Employee.ID AS EmployeeID,
        SUM(TR_SalesReturnDetails.ReturnQuantity) AS ReturnQty
    FROM
        TR_SalesReturnHeader
    INNER JOIN
        TR_SalesReturnDetails ON TR_SalesReturnDetails.SalesReturnID = TR_SalesReturnHeader.ID
    INNER JOIN 
        MD_ItemMaster ON TR_SalesReturnDetails.ItemID  =  MD_ItemMaster.ID
    INNER JOIN 
        MD_Supplier ON MD_ItemMaster.SupplierID= MD_Supplier.ID
    INNER JOIN 
        MD_Employee ON TR_SalesReturnHeader.CreatedBy = MD_Employee.ID
    WHERE
        MD_Supplier.ID = 3 
        AND MD_ItemMaster.ID = 572
    GROUP BY
        MD_Employee.ID
) AS source
ON target.Employee = source.EmployeeID
WHEN MATCHED THEN 
    UPDATE SET target.ReturnQty = source.ReturnQty
WHEN NOT MATCHED THEN
    INSERT (Employee, ChargedQty, ReturnQty)
    VALUES (source.EmployeeID, 0, source.ReturnQty);

已执行两次插入后的去重方案 #

如果已经执行了两次插入生成了重复数据，直接对表变量做一次聚合查询即可得到合并后的结果：

SELECT 
    Employee,
    SUM(ChargedQty) AS ChargedQty,
    SUM(ReturnQty) AS ReturnQty
FROM @WSotable
GROUP BY Employee

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