按订单数量计数销售数量

Question

我正在使用SQL Server 2008 R2，并在包含customerId，orderAmaunt，regionID的表上进行分析。我需要根据每个区域中的订单上的订单来计数不同类别中的订单数。如果类别中没有销售，请返回0。

Sample of data:
CustomerID | OrderAmount | RegionID
10001      |     50      |   801
10002      |     25      |   801
10003      |     200     |   802
10001      |     100     |   802
10002      |     20      |   802
...

And my expected result is:
RegionID  |  CategoryID  |  Num_of_Sales
801       |      1       |     2          -----Below 100
801       |      2       |     0          -----100-200
802       |      1       |     2          -----Below 100
802       |      2       |     1          -----100-200
...

我的问题是：1.如何在空的类别中返回0？2.有没有更好的编写代码的方法？(不使用Union(

WITH Category1 AS(
	SELECT * FROM Sales_Table 
	WHERE NewAmount <= 100
	)
, Category2 AS(
	SELECT * FROM Sales_Table 
	WHERE NewAmount BETWEEN 101 AND 200
	)
, [...]
SELECT Region_ID, CategoryID, Num_of_Sales
FROM (
	SELECT Region_ID, COUNT(*) AS [Num_of_Sales], 1 AS CategoryID
		FROM Category1
		GROUP BY Region_ID
UNION
	SELECT Region_ID, COUNT(*) AS [Num_of_Sales], 2 AS CategoryID
		FROM Category2
		GROUP BY Region_ID
UNION
	[...]
)z
ORDER BY Region_ID, CategoryID

所以，我使用这些代码并获得结果，但是在区域801的100-200类别中，计数没有返回0。

Answer 1

您要实现的目标需要一个表保存区域ID和categoryId。然后，我们可以使用该表进行连接，如下所示。

 With RegCatSales as
    (   
    select RegionID,C,COUNT(*) AS [Num_of_Sales]
        from 
            (
                select RegionID,OrderAmount,
                CASE
                    WHEN OrderAmount <= 100 THEN 1
                    WHEN OrderAmount BETWEEN 101 AND 200 THEN 2
                END as C
                from Sales_Table x  
            ) xx 
        group by RegionID, C
    ), 
    Regions as
    (
        select distinct RegionID from RegCatSales
    ), 
    Categories as 
    (
        select distinct C from RegCatSales
    ), 
    RegCat AS(
        select distinct RegionID, C as CategoryID from Regions,Categories
    )

    select rc.RegionID,rc.CategoryID, ISNULL([Num_of_Sales],0) NUM_Of_Sales  from  
    RegCatSales rcs
    right join RegCat rc
    on rc.RegionID= rcs.RegionID and rc.CategoryID = rcs.C
    order by rc.RegionID, rc.CategoryID