TableA:
id int
name varchar
registerTime datetimeid name registerTime
1 张三 2005-11-8 23:15:22
2 张三2 2005-11-14 21:25:35
3 张三3 2005-11-18 20:15:42
4 张三4 2005-12-4 23:44:22
5 张三5 2005-12-9 22:15:22
6 张三5 2005-12-10 9:25:39
7 张三6 2005-12-15 20:55:36
8 张三7 2005-12-18 23:15:12现在我想查询出2005-11-14到2005-12-15之间每天注册的人数,就是如下结果
2005-11-14 1
2005-11-15 0
2005-11-16 0
2005-11-17 0
2005-11-18 1
...
用sql语句能实现吗?
id int
name varchar
registerTime datetimeid name registerTime
1 张三 2005-11-8 23:15:22
2 张三2 2005-11-14 21:25:35
3 张三3 2005-11-18 20:15:42
4 张三4 2005-12-4 23:44:22
5 张三5 2005-12-9 22:15:22
6 张三5 2005-12-10 9:25:39
7 张三6 2005-12-15 20:55:36
8 张三7 2005-12-18 23:15:12现在我想查询出2005-11-14到2005-12-15之间每天注册的人数,就是如下结果
2005-11-14 1
2005-11-15 0
2005-11-16 0
2005-11-17 0
2005-11-18 1
...
用sql语句能实现吗?
dateadd(day,t.a,'2005-11-14') as date,
isnull(t1.cnt,0) as cnt
from (
select 0 as a
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
...
union all
select 30
) as t
left join (
select
cast(convert(char(8),registerTime,112) as datetime) as date,
count(*) as cnt
from tableA
where registerTime>='2005-11-14'
and registerTime<'2005-12-16'
group by cast(convert(char(8),registerTime,112) as datetime)
) as t1
on dateadd(day,t.a,'2005-11-14')=t2.date
drop table #tmp
GO
declare @t table(id int,name varchar(10),registerTime datetime)
insert @t
select 1, '张三', '2005-11-8 23:15:22' union all
select 2, '张三2', '2005-11-14 21:25:35' union all
select 3, '张三3', '2005-11-18 20:15:42' union all
select 4, '张三4', '2005-12-4 23:44:22' union all
select 5, '张三5', '2005-12-9 22:15:22' union all
select 6, '张三5', '2005-12-10 9:25:39' union all
select 7, '张三6', '2005-12-15 20:55:36' union all
select 8, '张三7', '2005-12-18 23:15:12'----生成天数临时表
select top 40 id = identity(int,0,1) into #tmp from syscolumns,sysobjects----查询
SELECT
convert(varchar(10),dateadd(day,a.id,'2005-11-14'),120) as registerTime,
isnull(b.num,0) as num
FROM #tmp as a
LEFT JOIN
(select convert(varchar(10),registerTime,120) as registerTime,count(*) as num
from @t group by convert(varchar(10),registerTime,120)) as b
ON dateadd(day,a.id,'2005-11-14') = b.registerTime
WHERE dateadd(day,a.id,'2005-11-14') <= '2005-12-15'----清除测试环境
drop table #tmp
/*结果
registerTime num
------------ -----------
2005-11-14 1
2005-11-15 0
2005-11-16 0
2005-11-17 0
2005-11-18 1
2005-11-19 0
2005-11-20 0
2005-11-21 0
2005-11-22 0
2005-11-23 0
2005-11-24 0
2005-11-25 0
2005-11-26 0
2005-11-27 0
2005-11-28 0
2005-11-29 0
2005-11-30 0
2005-12-01 0
2005-12-02 0
2005-12-03 0
2005-12-04 1
2005-12-05 0
2005-12-06 0
2005-12-07 0
2005-12-08 0
2005-12-09 1
2005-12-10 1
2005-12-11 0
2005-12-12 0
2005-12-13 0
2005-12-14 0
2005-12-15 1
*/