T-SQL，连接上不匹配的零和

Question

我有两张表，一张包含我所有的分支机构，一张包含我所有的销售额。 销售表还包含销售代表 ID、分支机构 ID、月份和年份。

我需要一个查询，该查询将返回特定代表一年的销售额总和（按分支机构和月份分组），并且如果该月分支机构没有销售额，则该查询必须返回 0。 我有以下内容，如果没有销售，则不会返回 0：

SELECT
    s.Month,
    b.BranchName,
    SUM(s.InvoiceAmount) AS 'Sales'
FROM 
    Branch b
INNER JOIN
    Sales s ON s.BranchID = b.BranchID
WHERE
    s.Year = 2008
AND
    s.SalesRepID= 11
GROUP BY 
    s.Month,
    b.BranchName
ORDER BY 
    s.Month,
    b.BranchName

Answer 1

您需要使用左连接和 isnull 来获得正确的总和：

SELECT    b.BranchName
,    SUM(isnull(s.InvoiceAmount, 0)) AS 'Sales'
FROM     Branch b
LEFT JOIN    Sales s ON s.BranchID = b.BranchID 
GROUP BY     s.Month,    b.BranchName
ORDER BY     s.Month,    b.BranchName

如果销售员在特定月份没有销售额，您仍然需要对其进行更多操作才能显示月份。

Answer 2

我将连接从内部更改为左外部，并为没有销售的分支添加了 ISNULL 函数。

SELECT

    b.BranchName,
    s.Month,
    SUM(ISNULL(s.InvoiceAmount,0)) AS 'Sales'
FROM 
    Branch b
LEFT JOIN
    Sales s ON s.BranchID = b.BranchID
WHERE
    s.Year = 2008
AND
    s.SalesRepID= 11
GROUP BY 
    s.Month,
    b.BranchName
ORDER BY 
    s.Month,
    b.BranchName

Answer 3

您需要添加“缺失”的数据才能加入。

SELECT
    b.BranchName,
    SUM(ISNULL(s.InvoiceAmount, 0)) AS 'Sales',
    s.Month
FROM 
    Branch b
    LEFT OUTER JOIN (
            SELECT
        b.BranchID AS BranchID
        , s.SalesRepID AS SalesRepID
        , Months.Month AS Month
        , Years.Year AS Year
        , 0 AS InvoiceAmount
      FROM 
          Sales s
          CROSS JOIN (
            SELECT 1 AS Month
            UNION ALL SELECT 2
            UNION ALL SELECT 3
            UNION ALL SELECT 4
            UNION ALL SELECT 5
            UNION ALL SELECT 6
            UNION ALL SELECT 7
            UNION ALL SELECT 8
            UNION ALL SELECT 9
            UNION ALL SELECT 10
            UNION ALL SELECT 11
            UNION ALL SELECT 12
            ) Months
          CROSS JOIN (
            SELECT 2007 AS Year
            UNION ALL SELECT 2008
            UNION ALL SELECT 2009
            ) Years
          CROSS JOIN Branch b
      UNION ALL SELECT 
        s.BranchID AS BranchID
        , s.SalesRepID AS SalesRepID
        , s.Month AS Month
        , s.Year AS Year
        , s.InvoiceAmount AS InvoiceAmount
      FROM Sales s      
    )s ON s.BranchID = b.BranchID    
WHERE
    s.Year = 2008
    AND s.SalesRepID= 11
GROUP BY 
    s.Month,
    b.BranchName
ORDER BY 
    b.BranchName,
    s.Month

Answer 4

您需要对 Sales 执行 LEFT JOIN，以便返回 Sales 表中没有任何记录的代表。

Answer 5

如果您的查询返回 NULL，您可以使用其中一种合并方法：COALESCE(SUM(...), 0) 将返回列表中的第一个非 NULL 值...