• 主页
• 基础类
• 应用实例
• 新技术前沿

# 我怎样得出1-24小时的数据

2007-12-10 05:20:13.000
2007-11-21 08:20:40.000
2007-12-03 04:21:31.000
2007-11-04 10:21:04.000

SELECT DATEPART(hh,register_time),count(register_time)
from [RegisterInfo]
group by DATEPART(hh,register_time)

4 1
5 1
8 1
10 1

1 0
2 0
3 0
4 1
5 1
6 0
7 0
8 1
9 0
10 1
...
24 0

...全文
47 点赞 收藏 4

4 条回复

dawugui 2007-12-06
``````select t1.h 小时 , isnull(t2.数量,0) 数量 from
(select 1 as h union select 2 union select 3 union select 4 union select 5
union select 6 union select 7 union select 8 union select 9
union select 10 union select 11 union select 12 union select 13
union select 14 union select 15 union select 16 union select 17
union select 18 union select 19 union select 20 union select 21
union select 22 union select 23 union select 24) t1
left join
(
SELECT h = DATEPART(hh,register_time),数量 = count(register_time)
from [RegisterInfo]
group by DATEPART(hh,register_time)
) t2
on t1.h = t2.h``````

-狙击手- 2007-12-06
``````select top 24 identity(int,0,1) as N into # from sysobjects a,sysobjects b

select b.n,sum(a.数量) as 数量
from # b
left join tabea a
on b.n = datepart(hh,datecole)
group by b.n

drop table #``````

chuifengde 2007-12-06
``````declare @a table(id int identity(0,1),a int)
insert @a select top 24 0 from syscolumns

declare @b table(a datetime)
insert @b select '2007-12-10 05:20:13.000'
union all select '2007-11-21 08:20:40.000'
union all select '2007-12-03 04:21:31.000'
union all select '2007-11-04 10:21:04.000'

select id,isnull(x,0) num from @a a
left join
(select datepart(hour,a)a,count(1) x from @b group by datepart(hour,a))b
on a.id=b.a

--result
/*
id          num
----------- -----------
0           0
1           0
2           0
3           0
4           1
5           1
6           0
7           0
8           1
9           0
10          1
11          0
12          0
13          0
14          0
15          0
16          0
17          0
18          0
19          0
20          0
21          0
22          0
23          0

（所影响的行数为 24 行）

*/``````

MS-SQL Server

3.2w+

MS-SQL Server相关内容讨论专区