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

求一個sql語句---統計!難,請高手幫助!

molan68 2007-12-24 02:50:24
求一條 sql語句,見下表:
Stime jobCode
2007-12-20 18:00:02 e
2007-12-20 8:30:09 r
2007-12-20 6:44:11 f
2007-12-20 5:02:12 a
2007-12-20 2:50:22 c
2007-12-20 0:00:06 c
按時間劃分,每兩小時一節,0-2,2-4,4-6。。。22-0
統計得如何(每天12節顯示,jobCode有數據表示1,無表示0):
節數 有無數據
1 1
2 1
3 0
4 1
5 1
6 0
7 0
8 0
9 0
10 1
11 0
12 0
請朋友幫助!謝謝!
...全文
76 点赞 收藏 14
写回复
14 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
ljking0731 2007-12-24
--建表
create table #TimeDistribute(Stime datetime,jobCode char(2))
insert into #TimeDistribute
select '2007-12-20 18:00:02','e'
union all select '2007-12-20 8:30:09','r'
union all select '2007-12-20 6:44:11','f'
union all select '2007-12-20 5:02:12','a'
union all select '2007-12-20 2:50:22','c'
union all select '2007-12-20 0:00:06','c'
--生成完整的部分
declare @part int
declare @sql varchar(1000)
set @sql='select ''0-2'' as part,1 as section '
set @part=2
while @part<24
begin
set @sql=@sql+'union all select '''+rtrim(cast(@part as char(2)))+'-'+rtrim(cast(@part+2 as char(2)))+''','+cast(@part/2+1 as char(2))+' '
set @part=@part+2
end
set @sql='select part,section , sum(case when (datepart(hour,stime)>=section) and (datepart(hour,stime)<=section+1) then 1 else 0 end ) as counts from #TimeDistribute,('+@sql+') as b group by part,section '

exec(@sql)
--结果
part section counts
----- ----------- -----------
0-2 1 1
2-4 2 1
4-6 3 0
6-8 4 1
8-10 5 2
10-12 6 1
12-14 7 1
14-16 8 1
16-18 9 0
18-20 10 0
20-22 11 0
22-24 12 0
回复
wzy_love_sly 2007-12-24

declare @tb table (Stime datetime,jobcode varchar(10))
insert into @tb select '2007-12-20 18:00:02','e'
insert into @tb select '2007-12-20 8:30:09','r'
insert into @tb select '2007-12-20 6:44:11','f'
insert into @tb select '2007-12-20 5:02:12','a'
insert into @tb select '2007-12-20 2:50:22','c'
insert into @tb select '2007-12-20 0:00:06','c'

select a.節數, sum(case when stime is null then 0 else 1 end) as 有無數據 from (
select 1 as 節數 union all
select 2 as 節數 union all
select 3 as 節數 union all
select 4 as 節數 union all
select 5 as 節數 union all
select 6 as 節數 union all
select 7 as 節數 union all
select 8 as 節數 union all
select 9 as 節數 union all
select 10 as 節數 union all
select 11 as 節數 union all
select 12 as 節數
)a left join @tb b on a.節數=(case when datepart(hh,Stime)=0 then 24 else datepart(hh,Stime) end)*2/4
group by a.節數


節數 有無數據
1 1
2 1
3 1
4 1
5 0
6 0
7 0
8 0
9 1
10 0
11 0
12 1
回复
-狙击手- 2007-12-24


declare @a table(id int, jobCode char(1), Stime smalldatetime)
insert @a select 1, 'e', '2007-12-20 18:00:02'
union all select 2, 'r', '2007-12-20 8:30:09'
union all select 3, 'f', '2007-12-20 6:44:11'
union all select 4, 'a', '2007-12-20 5:02:12'
union all select 5, 'c', '2007-12-20 2:50:22'
union all select 6, 'c', '2007-12-20 0:00:06'

select top 12 identity(int,0,2) as a,0 as b into # from sysobjects
update # set b = a+2

select right(100+a,2)+':00-'+right(100+b,2)+':00',
sum(case when datepart(hour,Stime) >=a and datepart(hour,Stime)<b then 1 else 0 end)
from @a a ,
# aa
group by right(100+a,2)+':00-'+right(100+b,2)+':00'

drop table #
/*

--------------- -----------
00:00-02:00 1
02:00-04:00 1
04:00-06:00 1
06:00-08:00 1
08:00-10:00 1
10:00-12:00 0
12:00-14:00 0
14:00-16:00 0
16:00-18:00 0
18:00-20:00 1
20:00-22:00 0
22:00-24:00 0

(所影响的行数为 12 行)
*/

回复
dawugui 2007-12-24
--这个按你的要求显示成1,2,3,4...12
create table tb(Stime datetime, jobCode varchar(10))
insert into tb values('2007-12-20 18:00:02', 'e')
insert into tb values('2007-12-20 8:30:09' , 'r')
insert into tb values('2007-12-20 6:44:11' , 'f')
insert into tb values('2007-12-20 5:02:12' , 'a')
insert into tb values('2007-12-20 2:50:22' , 'c')
insert into tb values('2007-12-20 0:00:06' , 'c')
go
select 时间段 = aa.a/2 + 1,
数量 = sum(case when datepart(hour,Stime) >=a and datepart(hour,Stime)<b then 1 else 0 end)
from tb a ,
(select 0 a,2 b
union all select 2,4
union all select 4,6
union all select 6,8
union all select 8,10
union all select 10,12
union all select 12,14
union all select 14,16
union all select 16,18
union all select 18,20
union all select 20,22
union all select 22,24
)aa
group by aa.a/2 + 1

drop table tb

/*
时间段 数量
----------- -----------
1 1
2 1
3 1
4 1
5 1
6 0
7 0
8 0
9 0
10 1
11 0
12 0

(12 行受影响)
*/
回复
molan68 2007-12-24
兄弟們,我先看一會兒不會影響加分你們吧?!
回复
dawugui 2007-12-24
wzy_love_sly

兄弟,你结贴率高不?

-------------

哈哈.

回复
dawugui 2007-12-24
--上面漏了几个时间段,不好意思.
create table tb(Stime datetime, jobCode varchar(10))
insert into tb values('2007-12-20 18:00:02', 'e')
insert into tb values('2007-12-20 8:30:09' , 'r')
insert into tb values('2007-12-20 6:44:11' , 'f')
insert into tb values('2007-12-20 5:02:12' , 'a')
insert into tb values('2007-12-20 2:50:22' , 'c')
insert into tb values('2007-12-20 0:00:06' , 'c')
go
select 时间段 = right(100+a,2)+':00-'+right(100+b,2)+':00',
数量 = sum(case when datepart(hour,Stime) >=a and datepart(hour,Stime)<b then 1 else 0 end)
from tb a ,
(select 0 a,2 b
union all select 2,4
union all select 4,6
union all select 6,8
union all select 8,10
union all select 10,12
union all select 12,14
union all select 14,16
union all select 16,18
union all select 18,20
union all select 20,22
union all select 22,24
)aa
group by right(100+a,2)+':00-'+right(100+b,2)+':00'

drop table tb

/*
时间段 数量
--------------- -----------
00:00-02:00 1
02:00-04:00 1
04:00-06:00 1
06:00-08:00 1
08:00-10:00 1
10:00-12:00 0
12:00-14:00 0
14:00-16:00 0
16:00-18:00 0
18:00-20:00 1
20:00-22:00 0
22:00-24:00 0

(12 行受影响)
*/
回复
dawugui 2007-12-24
create table tb(Stime datetime, jobCode varchar(10))
insert into tb values('2007-12-20 18:00:02', 'e')
insert into tb values('2007-12-20 8:30:09' , 'r')
insert into tb values('2007-12-20 6:44:11' , 'f')
insert into tb values('2007-12-20 5:02:12' , 'a')
insert into tb values('2007-12-20 2:50:22' , 'c')
insert into tb values('2007-12-20 0:00:06' , 'c')
go
select 时间段 = right(100+a,2)+':00-'+right(100+b,2)+':00',
数量 = sum(case when datepart(hour,Stime) >=a and datepart(hour,Stime)<b then 1 else 0 end)
from tb a ,
(select 0 a,2 b
union all select 2,4
union all select 6,8
union all select 10,12
union all select 14,16
union all select 16,18
union all select 18,20
union all select 20,22
union all select 22,24
)aa
group by right(100+a,2)+':00-'+right(100+b,2)+':00'

drop table tb

/*
时间段 数量
--------------- -----------
00:00-02:00 1
02:00-04:00 1
06:00-08:00 1
10:00-12:00 0
14:00-16:00 0
16:00-18:00 0
18:00-20:00 1
20:00-22:00 0
22:00-24:00 0

(9 行受影响)
*/
回复
molan68 2007-12-24
問題OK,馬上結!待請教!
回复
wzy_love_sly 2007-12-24
呵呵,哥们怕了
回复
-狙击手- 2007-12-24
哈哈,LS吓坏了
回复
molan68 2007-12-24
高!
回复
wzy_love_sly 2007-12-24
兄弟,你结贴率高不?
回复
dawugui 2007-12-24
--这个按没小时统计,差不多.
比如有一个表timeTable,有字段id,score,inputDate.
数据如下
id score inputDate
1 2 '2007-4-5 3:33:33'
2 1 '2007-4-5 4:33:33'
3 4 '2007-3-5 3:33:33'
4 2 '2007-4-2 2:33:33'
我要按时间分组统计score,结果如下
0:00-1:00 0
1:00-2:00 0
2:00-3:00 2
3:00-4:00 6
4:00-5:00 1
5:00-6:00 0
....
23:00-24:00 0

declare @a table(id int, score int, inputDate smalldatetime)
insert @a select 1, 2, '2007-4-5 3:33:33'
union all select 2, 1, '2007-4-5 4:33:33'
union all select 3, 4, '2007-3-5 3:33:33'
union all select 4, 2, '2007-4-2 2:33:33'

select right(100+a,2)+':00-'+right(100+b,2)+':00',
sum(case when datepart(hour,inputdate) >=a and datepart(hour,inputdate)<b then score else 0 end)
from @a a ,
(select 0 a,1 b
union all select 1,2
union all select 2,3
union all select 3,4
union all select 4,5
union all select 5,6
union all select 6,7
union all select 7,8
union all select 8,9
union all select 9,10
union all select 10,11
union all select 11,12
union all select 12,13
union all select 13,14
union all select 14,15
union all select 15,16
union all select 16,17
union all select 17,18
union all select 18,19
union all select 19,20
union all select 20,21
union all select 21,22
union all select 22,23
union all select 23,24
)aa
group by right(100+a,2)+':00-'+right(100+b,2)+':00'



--------------- -----------
00:00-01:00 0
01:00-02:00 0
02:00-03:00 2
03:00-04:00 6
04:00-05:00 1
05:00-06:00 0
06:00-07:00 0
07:00-08:00 0
08:00-09:00 0
09:00-10:00 0
10:00-11:00 0
11:00-12:00 0
12:00-13:00 0
13:00-14:00 0
14:00-15:00 0
15:00-16:00 0
16:00-17:00 0
17:00-18:00 0
18:00-19:00 0
19:00-20:00 0
20:00-21:00 0
21:00-22:00 0
22:00-23:00 0
23:00-24:00 0

(所影响的行数为 24 行)
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告