SQL计数日期范围 [英] SQL count date range

问题描述

我试图查询我的SQL数据库，以获取每个客户在特定日期范围内所做的订单数量。

我有一个订单列表遵循

  CustomerName ClientID DateOrdered 
客户二号10 2011-11-25 
客户3号11 2011-10-15 
客户3号11 2011-11-25 
  

和我想知道特定客户在2011-11-1和2011-11-30之间订立了多少订单，这将导致：

客户名称ClientID号码
客户号码3 11 1
客户号码2 1

到目前为止，我设法得到这个

  SELECT CustomerName， ClientID，COUNT（*）AS号码
从订单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）
  

哪个给我客户所做的每一个订单

  CustomerName ClientID号码
客户3 11 2 
客户2号10 1 
  

我正在解决这个问题，还是有一个更简单的方法，我完全忽略了！

$ b $从
（
）中选择CustomerName，ClientID，count（*）
b

解决方案

从订单
where datediff（mm，DateOrdered，getdate（））< = 1
）a
group by CustomerName，ClientID

这样做是利用过滤行的子查询在某一个月的日期（这似乎是你正在寻找）。然后通过 CustomerName 和 ClientID 分组，并获得其订单总和。

I'm trying to query my SQL database to get the number of orders made by each client within a certain date range.

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 :

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!

解决方案

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 CustomerName and ClientID and gets the sum of their orders.

这篇关于SQL计数日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！