求一SQL语句，关于按天数分组的
TableA:
id int
name varchar
registerTime datetime
id 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语句能实现吗？
[解决办法]
select
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
[解决办法]
if object_id( 'tempdb..#tmp ') is not null
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
*/
