- http://www.itnose.net/detail/6009311.html
- select years(orderdate),
- month(OrderDate),count(1)
- from
- tb
- group by years(orderdate),
- month(OrderDate)
- -- Author :fredrickhu(小F,向高手学习)
- -- Date :2014-03-17 15:48:38
- -- Verstion:
- -- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
- -- Jul 9 2008 14:43:34
- -- Copyright (c) 1988-2008 Microsoft Corporation
- -- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
- --
- ----------------------------------------------------------------
- --> 测试数据:[tb]
- if object_id('[tb]') is not null drop table [tb]
- go
- create table [tb]([O_Id] int,[OrderDate] datetime,[OrderPrice] int,[Customer] varchar(6))
- insert [tb]
- select 1,'2008/12/29',1000,'Bush' union all
- select 2,'2008/11/23',1600,'Carter' union all
- select 3,'2008/11/05',700,'Bush' union all
- select 4,'2008/09/28',300,'Bush' union all
- select 5,'2008/08/06',2000,'Adams' union all
- select 6,'2008/08/21',100,'Carter'
- --------------开始查询--------------------------
- declare @startdate datetime,@enddate datetime
- set @startdate='2008-01-01'
- set @enddate='2009-12-31'
- SELECT * INTO #tb
- FROM
- (
- select
- DISTINCT YEAR(convert(varchar(10),dateadd(day,number,@startdate),120)) AS [year],MONTH(convert(varchar(10),dateadd(day,number,@startdate),120)) AS [month]
- from
- master..spt_values
- where
- datediff(day,dateadd(day,number,@startdate), @enddate)>=0
- and number>=0
- and type='p'
- )t
- --SELECT * FROM #tb AS a LEFT JOIN tb AS b ON a.[year]=DATEPART(YEAR,b.OrderDate) AND a.[month]=DATEPART(month,b.OrderDate)
- SELECT a.*,ISNULL(COUNT(b.O_Id),0) AS 客户数量 FROM #tb AS a LEFT JOIN tb AS b ON a.[year]=DATEPART(YEAR,b.OrderDate) AND a.[month]=DATEPART(month,b.OrderDate) GROUP BY a.year,a.month ORDER BY a.year
- DROP TABLE #tb
- ----------------结果----------------------------
- /* year month 客户数量
- ----------- ----------- -----------
- 2008 1 0
- 2008 2 0
- 2008 3 0
- 2008 4 0
- 2008 5 0
- 2008 6 0
- 2008 7 0
- 2008 8 2
- 2008 9 1
- 2008 10 0
- 2008 11 2
- 2008 12 1
- 2009 1 0
- 2009 2 0
- 2009 3 0
- 2009 4 0
- 2009 5 0
- 2009 6 0
- 2009 7 0
- 2009 8 0
- 2009 9 0
- 2009 10 0
- 2009 11 0
- 2009 12 0
- 警告: 聚合或其他 SET 操作消除了 Null 值。
- (24 行受影响)
- */