SQL 关于时间的查询问题 高分答谢

[xwdxwd]

我有一个表如下：
表名为： bite
pm rksj cksj
1 2007-6-9 06:23:09 2007-6-9 12:55:10
2 2007-6-9 06:22:19 2007-6-9 16:22:23
3 2007-6-9 16:19:24 2007-6-9 23:32:14
4 2007-6-9 15:16:55 2007-6-9 23:35:26
5 2007-6-9 18:05:51 2007-6-10 12:45:28

我把一天分为四个时间段(sjd) 为 一段（6点到12点） 二段（12点到18点） 三段（18点到24点） 四段（24点到6点）
我想要这样的查询语句：判断表中记录是那个时间段的记录 并且在库中时间是多少(sc)
得出记录存入下边这个表
pm rksj cksj sjd sc

我想得出记录应该是这样的
pm rksj cksj sjd sc
1 2007-6-9 06:23:09 2007-6-9 16:55:10 一段 6.5
2 2007-6-9 12:22:19 2007-6-9 16:22:23 二段 4
3 2007-6-9 16:19:24 2007-6-9 23:32:14 二段三段 7
4 2007-6-9 15:16:55 2007-6-9 23:35:26 二段三段 8
5 2007-6-9 18:05:51 2007-6-10 12:45:28 三段四段一段 12.5

求高手帮忙 分不够追加

[renzhe02]

测试数据

create table bite(pm int,rksj datetime,cksj datetime)

insert into bite
select 1,'2007-6-9 06:23:09','2007-6-9 12:55:10' union 
select 2, '2007-6-9 06:22:19','2007-6-9 16:22:23'  union 
select 3,'2007-6-9 16:19:24', '2007-6-9 23:32:14 ' union 
select 4 ,'2007-6-9 15:16:55', '2007-6-9 23:35:26 ' union 
select 5 ,'2007-6-9 18:05:51','2007-6-10 12:45:28 '

[renzhe02]

create table bite(pm int,rksj datetime,cksj datetime)

insert into bite
select 1,'2007-6-9 06:23:09','2007-6-9 12:55:10' union 
select 2, '2007-6-9 06:22:19','2007-6-9 16:22:23'  union 
select 3,'2007-6-9 16:19:24', '2007-6-9 23:32:14 ' union 
select 4 ,'2007-6-9 15:16:55', '2007-6-9 23:35:26 ' union 
select 5 ,'2007-6-9 18:05:51','2007-6-10 12:45:28 '

--select * from bite

select pm,rksj,cksj,
case when datepart(hour,rksj) between 6 and 12 then '一段'
else case when datepart(hour,rksj) between 12 and 18 then '二段'
else case when datepart(hour,rksj) between 12 and 18 then '二段'
else case when datepart(hour,rksj) between 18 and 24 then '三段'
else '四段' end end end end as sjd,
datediff(hour,rksj,cksj) as sc1
from bite

/*
1	2007-06-09 06:23:09.000	2007-06-09 12:55:10.000	一段	6
2	2007-06-09 06:22:19.000	2007-06-09 16:22:23.000	一段	10
3	2007-06-09 16:19:24.000	2007-06-09 23:32:14.000	二段	7
4	2007-06-09 15:16:55.000	2007-06-09 23:35:26.000	二段	8
5	2007-06-09 18:05:51.000	2007-06-10 12:45:28.000	二段	18

*/

[renzhe02]

不好意思
没有完全满足要求
晚上再来做

[-狙击手-]

case when

[fcuandy]

http://topic.csdn.net/u/20071121/10/672b83d1-9333-4073-a155-5b85dc1bde60.html
跟这个很类似,不过这个是按8小时分三段.

[-狙击手-]

create table bite(pm int,rksj datetime,cksj datetime)

insert into bite
select 1,'2007-6-9 06:23:09','2007-6-9 12:55:10' union 
select 2, '2007-6-9 06:22:19','2007-6-9 16:22:23'  union 
select 3,'2007-6-9 16:19:24', '2007-6-9 23:32:14 ' union 
select 4 ,'2007-6-9 15:16:55', '2007-6-9 23:35:26 ' union 
select 5 ,'2007-6-9 18:05:51','2007-6-10 12:45:28 '



go

--一段（6点到12点）   二段（12点到18点）   三段（18点到24点）   四段（24点到6点）
select pm,
case when datepart(hh,rksj) between 6 and 12 and datepart(hh,cksj) between 6 and 12 and datepart(d,rksj) = datepart(d,cksj) then '一段' 
     when datepart(hh,rksj) between 6 and 12 and datepart(hh,cksj) between 12 and 18 and datepart(d,rksj) = datepart(d,cksj) then '一段二段'	
     when datepart(hh,rksj) between 6 and 12 and datepart(hh,cksj) between 18 and 24 and datepart(d,rksj) = datepart(d,cksj) then '一段二段三段'
     when datepart(hh,rksj) between 6 and 12 and datepart(hh,cksj) between 0 and 6 and datepart(d,rksj)+1 = datepart(d,cksj) then '一段二段三段四段'	
     when datepart(hh,rksj) between 12 and 18 and datepart(hh,cksj) between 12 and 18 and datepart(d,rksj) = datepart(d,cksj) then '二段'
     when datepart(hh,rksj) between 12 and 18 and datepart(hh,cksj) between 18 and 24 and datepart(d,rksj) = datepart(d,cksj) then '二段三段'
     when datepart(hh,rksj) between 12 and 18 and datepart(hh,cksj) between 0 and 6 and datepart(d,rksj)+1 = datepart(d,cksj) then '二段三段四段'
     when datepart(hh,rksj) between 12 and 18 and datepart(hh,cksj) between 6 and 12 and datepart(d,rksj)+1 = datepart(d,cksj) then '二段三段四段一段'
     when datepart(hh,rksj) between 18 and 24 and datepart(hh,cksj) between 18 and 24 and datepart(d,rksj) = datepart(d,cksj) then '三段'
     when datepart(hh,rksj) between 18 and 24 and datepart(hh,cksj) between 0 and 6and datepart(d,rksj)+1 = datepart(d,cksj)  then '三段四段'
     when datepart(hh,rksj) between 0 and 6 and datepart(hh,cksj) between 0 and 6 and datepart(d,rksj) = datepart(d,cksj) then '四段'
     when datepart(hh,rksj) between 0 and 6 and datepart(hh,cksj) between 12 and 18 and datepart(d,rksj) = datepart(d,cksj) then '四段一段'
     when datepart(hh,rksj) between 0 and 6 and datepart(hh,cksj) between 18 and 24 and datepart(d,rksj) = datepart(d,cksj) then '四段一段二段'


     
     end,
     sc = round(datediff(mi,rksj,cksj)*1.0/60,1),rksj,cksj 
from bite

drop table bite
/*

pm                           sc                   rksj                                                   cksj                                                   
----------- ---------------- -------------------- ------------------------------------------------------ ------------------------------------------------------ 
1           一段               6.500000             2007-06-09 06:23:09.000                                2007-06-09 12:55:10.000
2           一段二段             10.000000            2007-06-09 06:22:19.000                                2007-06-09 16:22:23.000
3           二段三段             7.200000             2007-06-09 16:19:24.000                                2007-06-09 23:32:14.000
4           二段三段             8.300000             2007-06-09 15:16:55.000                                2007-06-09 23:35:26.000
5           二段三段四段一段         18.700000            2007-06-09 18:05:51.000                                2007-06-10 12:45:28.000

（所影响的行数为 5 行）
*/

[wzy_love_sly]

select pm,rksj,cksj,
case when datepart(hour,rksj) between 6 and 12 then '一段'
else case when datepart(hour,rksj) between 12 and 18 then '二段'
else case when datepart(hour,rksj) between 12 and 18 then '二段'
else case when datepart(hour,rksj) between 18 and 24 then '三段'
else '四段' end end end end as sjd,
case when datediff(n,rksj,cksj)%60 >30 
then cast(datediff(hh,rksj,cksj) as varchar)+'.5'
else cast(datediff(hh,rksj,cksj) as varchar)
 end as sc1

from bite

用忍者的

[wzy_love_sly]

1 2007-06-09 06:23:09.000 2007-06-09 12:55:10.000 一段 6.5
2 2007-06-09 06:22:19.000 2007-06-09 16:22:23.000 一段 10
3 2007-06-09 16:19:24.000 2007-06-09 23:32:14.000 二段 7
4 2007-06-09 15:16:55.000 2007-06-09 23:35:26.000 二段 8
5 2007-06-09 18:05:51.000 2007-06-10 12:45:28.000 二段 18.5

[renzhe02]

狙击手 已经解决了啊