我有一个表如下:
表名为: 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求高手帮忙 分不够追加
表名为: 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求高手帮忙 分不够追加
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 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 biteselect 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*/
跟这个很类似,不过这个是按8小时分三段.
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 bitedrop 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 行)
*/
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 sc1from bite
用忍者的