在我们做各类统计和各类报表的时候，会有各种各样的查询要求、条件
这篇主要记录一个常见的统计查询
要求如下：
统计一段时间内，每天注册人数，如果某天没有人注册则显示为0
现在建个简单的表来试试
建表语句如下：
1 CREATE TABLE [dbo].[UserInfo]( 2 [UserID] [int] IDENTITY(1,1) NOT NULL, 3 [UserName] [varchar](50) NOT NULL, 4 [URegTime] [datetime] NOT NULL, 5 CONSTRAINT [PK_UserInfo] PRIMARY KEY CLUSTERED 6 ( 7 [UserID] ASC 8 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 9 ) ON [PRIMARY] 10 GO
然后向其插入一些数据
1 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher1','2016-03-01 8:00:00') 2 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher2','2016-03-01 9:00:00') 3 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher3','2016-03-05 8:00:00') 4 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher4','2016-03-05 20:00:00') 5 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher5','2016-03-07 8:00:00') 6 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher6','2016-03-09 8:00:00') 7 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher7','2016-03-11 8:00:00') 8 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher8','2016-03-11 18:00:00') 9 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher9','2016-03-15 8:00:00') 10 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher10','2016-03-17 8:00:00') 11 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher11','2016-03-17 11:00:00') 12 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher12','2016-03-17 13:00:00') 13 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher13','2016-03-18 8:00:00') 14 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher14','2016-03-19 8:00:00') 15 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher15','2016-03-20 8:00:00') 16 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher16','2016-03-20 11:00:00') 17 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher17','2016-03-20 12:00:00') 18 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher18','2016-03-21 8:00:00') 19 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher19','2016-03-21 9:00:00') 20 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher20','2016-03-22 8:00:00') 21 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher21','2016-03-22 12:00:00') 22 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher22','2016-03-26 8:00:00') 23 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher23','2016-03-26 10:00:00') 24 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher24','2016-03-26 11:00:00') 25 INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher25','2016-03-26 20:00:00')
下面来看看编写符合要求的sql查询
1 select b.regTime,userCount =isnull(a.userCount,0) 2 from 3 ( 4 select regTime=convert(varchar(10),dateadd(dd,number,'2016-03-01'),120) 5 from master..spt_values 6 where type='p' and number <= datediff(dd,'2016-03-01',GETDATE()) 7 ) b 8 left join 9 ( 10 select 11 userCount = COUNT(*), 12 regTime = CONVERT(varchar(4),YEAR(u.URegTime))+'-' +right(cast(month(u.URegTime)+100 as varchar),2) +'-'+right(cast(Day(u.URegTime)+100 as varchar),2) 13 from UserInfo u 14 group by YEAR(u.URegTime),Month(u.URegTime),Day(u.URegTime) 15 ) a on a.regTime = b.regTime
来看看结果，左边是原始数据，右边是统计数据
思路很简单，找出这段时间范围内(2016年3月1号到今天)的所有日期去跟我们相应的数据进行左连接即可！
其中用到了一个系统常量表 master..spt_values
一些相关这个表的具体内容可以看看下面的文章
Master..spt_values system table