记一个简单的sql查询

在我们做各类统计和各类报表的时候，会有各种各样的查询要求、条件

这篇主要记录一个常见的统计查询

要求如下：

统计一段时间内，每天注册人数，如果某天没有人注册则显示为0

现在建个简单的表来试试

建表语句如下：

CREATE TABLE [dbo].[UserInfo](
    [UserID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [varchar](50) NOT NULL,
    [URegTime] [datetime] NOT NULL,
 CONSTRAINT [PK_UserInfo] PRIMARY KEY CLUSTERED 
(
    [UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

 

然后向其插入一些数据

INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher1','2016-03-01 8:00:00')
INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher2','2016-03-01 9:00:00')
INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher3','2016-03-05 8:00:00')
INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher4','2016-03-05 20:00:00')
INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher5','2016-03-07 8:00:00')
INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher6','2016-03-09 8:00:00')
INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher7','2016-03-11 8:00:00')
INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher8','2016-03-11 18:00:00')
INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher9','2016-03-15 8:00:00')
INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher10','2016-03-17 8:00:00')
INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher11','2016-03-17 11:00:00')
INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher12','2016-03-17 13:00:00')
INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher13','2016-03-18 8:00:00')
INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher14','2016-03-19 8:00:00')
INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher15','2016-03-20 8:00:00')
INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher16','2016-03-20 11:00:00')
INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher17','2016-03-20 12:00:00')
INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher18','2016-03-21 8:00:00')
INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher19','2016-03-21 9:00:00')
INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher20','2016-03-22 8:00:00')
INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher21','2016-03-22 12:00:00')
INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher22','2016-03-26 8:00:00')
INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher23','2016-03-26 10:00:00')
INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher24','2016-03-26 11:00:00')
INSERT INTO [dbo].[UserInfo]([UserName],[URegTime])VALUES('catcher25','2016-03-26 20:00:00')

 

下面来看看编写符合要求的sql查询

select b.regTime,userCount =isnull(a.userCount,0)
from
(
select regTime=convert(varchar(10),dateadd(dd,number,'2016-03-01'),120)
from master..spt_values
where type='p'  and number <= datediff(dd,'2016-03-01',GETDATE())
) b
left join 
(
select 
userCount = COUNT(*),
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) 
from UserInfo u
group by YEAR(u.URegTime),Month(u.URegTime),Day(u.URegTime)
) a  on a.regTime  = b.regTime

来看看结果，左边是原始数据，右边是统计数据

        

 

思路很简单，找出这段时间范围内(2016年3月1号到今天)的所有日期去跟我们相应的数据进行左连接即可！

 

其中用到了一个系统常量表 master..spt_values

 

一些相关这个表的具体内容可以看看下面的文章

Master..spt_values system table

SQLServer——MASTER..spt_values

SQL Server 中master..spt_values的应用