SQL 计数日期范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8297080/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL count date range
提问by kiwijus
I'm trying to query my SQL database to get the number of orders made by each client within a certain date range.
我正在尝试查询我的 SQL 数据库以获取每个客户在特定日期范围内发出的订单数量。
I have a list of orders as follows
我有一个订单清单如下
CustomerName ClientID DateOrdered
Customer No.2 10 2011-11-25
Customer No.3 11 2011-10-15
Customer No.3 11 2011-11-25
and I want to be able to find out how many orders have been made by a specific client for example between 2011-11-1 and 2011-11-30, this should result in :
并且我希望能够找出特定客户在 2011 年 11 月 1 日和 2011 年 11 月 30 日之间发出的订单数量，这应该导致：
CustomerName ClientID Number
Customer No.3 11 1
Customer No.2 10 1
So far I've managed to get this
到目前为止，我已经设法得到了这个
SELECT CustomerName, ClientID, COUNT(*) AS Number
FROM Orders t
GROUP BY CustomerName, ClientID
HAVING COUNT(*) =
(SELECT MAX(Number)
FROM
(SELECT CustomerName, ClientID, COUNT(*) AS Number
FROM Orders
GROUP BY CustomerName, ClientID ) x
WHERE CustomerName = t.CustomerName )
Which gives me every order the customer has ever made
这给了我客户曾经下过的每一个订单
CustomerName ClientID Number
Customer No.3 11 2
Customer No.2 10 1
Am I going about the right way to solve this or is there a simpler way which I've completely overlooked!
我是在采用正确的方法来解决这个问题，还是有一种更简单的方法我完全忽略了！
回答by
select CustomerName, ClientID, count(*)
from
(
select CustomerName, ClientID
from Orders
where datediff(mm, DateOrdered, getdate()) <= 1
)a
group by CustomerName, ClientID
What this does is utilize a subquery that filters the rows by the dates in a given month (that seems to be what you are looking for). Then it groups by the CustomerNameand ClientIDand gets the sum of their orders.
这样做是利用一个子查询按给定月份的日期过滤行（这似乎是您正在寻找的）。然后它按CustomerNameand分组ClientID并获得它们的订单总和。
回答by Micha? Powaga
Should work fine:
应该可以正常工作：
select CustomerName, ClientID, count(*) as Number
from Orders
where DateOrdered between '20111101' and '20111130'
group by CustomerName, ClientID
回答by kiwijus
select ClientID, max(CustomerName), count(*)
from Orders
where DateOrdered between '2011-11-01' and '2011-11-30'
group by ClientID
Depending on database, the syntax of the DateOrdered selection may need to be varied.
根据数据库，DateOrdered 选择的语法可能需要变化。
回答by Prisoner
SELECT
c.CustomerName,
count(o.OrderID) as Orders
FROM
orders o
JOIN clients c ON o.ClientID = c.ClientID
WHERE
o.DateOrdered BETWEEN '2011-11-01' AND '2011-11-20'
GROUP BY c.ClientID ;
