SQL大小写操作

Question

我对sql相当陌生，并一直试图解决一个问题，你有一个关于订单的表信息。在本例中，我试图使用case操作来获取订单的月度报告，因此我应该有一个列表示年份，另一个列表示月份，然后我应该有1-20、21-22、23-24和25天以上的列。我试图使用案例操作来获得那些天发生的订单量。我尝试了以下查询:

SELECT 
DATEPART(YEAR,date) AS year,DATEPART(MONTH,date) AS month,
COUNT(CASE WHEN DATEPART(DAY,date) BETWEEN 1 AND 20 THEN order ELSE 0 END) AS D1_D20,
COUNT(CASE WHEN DATEPART(DAY,date) BETWEEN 21 AND 22 THEN order ELSE 0 END) AS D21_D22,
COUNT(CASE WHEN DATEPART(DAY,date) BETWEEN 23 AND 24 THEN order ELSE 0 END) AS D23_D24,
COUNT(CASE WHEN DATEPART(DAY,date) > 25 THEN order ELSE 0 END) AS D25_END
FROM ORDERS
GROUP BY DATEPART(YEAR,date),DATEPART(MONTH,date)

显然，这个查询的问题是，现在我只得到每天的订单总数，我知道我应该计算订单，但不知道语法。帮助将非常感激!

Answer 1

使用SUM():

SELECT 
DATEPART(YEAR, date) AS year, DATEPART(MONTH, date) AS month,
SUM(CASE WHEN DATEPART(DAY,date) BETWEEN 1 AND 20 THEN 1 ELSE 0 END) AS D1_D20,
SUM(CASE WHEN DATEPART(DAY,date) BETWEEN 21 AND 22 THEN 1 ELSE 0 END) AS D21_D22,
SUM(CASE WHEN DATEPART(DAY,date) BETWEEN 23 AND 24 THEN 1 ELSE 0 END) AS D23_D24,
SUM(CASE WHEN DATEPART(DAY,date) > 25 THEN 1 ELSE 0 END) AS D25_END
FROM ORDERS
GROUP BY DATEPART(YEAR, date), DATEPART(MONTH, date);

我建议使用DAY()、YEAR()和MONTH()函数，因为它们更容易输入。

顺便说一下，如果删除ELSE子句，可以使用COUNT()。您的特殊问题是COUNT(0) = COUNT(1)，因为COUNT()计数非NULL值。我更喜欢SUM()，因为它在这方面更直观。