T-SQL，在加入时不匹配的零和

I have two tables, one with all my Branches, and one with all my sales. The sales table also contains a Sales Rep ID, a Branch ID, a month and a year.

我有两张桌子,一张是我的所有分店,另一张是我的所有销售。销售表还包含销售代表ID,分支ID,月份和年份。

I need a query that will return the sum of a specific rep's sales for a year, grouped by branch and month, and the query must return 0 if there has been no sales in a branch for that month. I have the following, which does not return 0 if there are no sales:

我需要一个查询,它将返回特定代表销售一年的总和,按分支和月份分组,如果该月的分支中没有销售,则查询必须返回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

5 个解决方案

#1

You will need to add the "missing" data to be able to join it.

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

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

#2

You'll need to do a LEFT JOIN to Sales, so as to return even the reps that do not have any records in the Sales table.

您需要对Sales进行LEFT JOIN,以便返回Sales表中没有任何记录的rep。

#3

If your query is returning NULL, you can use one of the coalescing methods: COALESCE(SUM(...), 0) will return the first non-NULL value in the list...

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

#4

You need to use a left join and an isnull to get the sum right:

您需要使用左连接和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

You still need to do some more work with it to get the months showing too if a salesman has no sales in a particular month.

如果销售人员在特定月份没有销售,您仍然需要做更多的工作才能显示月份。

#5

I changed the join from inner to left outer and added the ISNULL function for branches with no sales.

我将连接从内部更改为左外部,并为没有销售的分支添加了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

#1