create table tb_Data
(realdata int,inserttime datetime)insert into tb_Data
select '123','2014-4-25 7:12:00'
union all select '123','2014-4-25 7:30:00'
union all select '123','2014-4-25 14:58:00'
union all select '123','2014-4-25 15:12:00'
union all select '123','2014-4-25 22:12:00'
union all select '123','2014-4-25 23:12:00'
union all select '121','2014-4-26 6:12:00'select realdata,inserttime,case
when DATEPART(HOUR,dateadd(hour,1,inserttime))<8 then '第三班'
when DATEPART(HOUR,dateadd(hour,1,inserttime))>=8 and DATEPART(HOUR,dateadd(hour,1,inserttime))<16 then '第一班'
when DATEPART(HOUR,dateadd(hour,1,inserttime))>=16 and DATEPART(HOUR,dateadd(hour,1,inserttime))<=23 then '第二班'
end as 第几班
from tb_Data
order by inserttime
(realdata int,inserttime datetime)insert into tb_Data
select '123','2014-4-25 7:12:00'
union all select '123','2014-4-25 7:30:00'
union all select '123','2014-4-25 14:58:00'
union all select '123','2014-4-25 15:12:00'
union all select '123','2014-4-25 22:12:00'
union all select '123','2014-4-25 23:12:00'
union all select '121','2014-4-26 6:12:00'select realdata,inserttime,case
when DATEPART(HOUR,dateadd(hour,1,inserttime))<8 then '第三班'
when DATEPART(HOUR,dateadd(hour,1,inserttime))>=8 and DATEPART(HOUR,dateadd(hour,1,inserttime))<16 then '第一班'
when DATEPART(HOUR,dateadd(hour,1,inserttime))>=16 and DATEPART(HOUR,dateadd(hour,1,inserttime))<=23 then '第二班'
end as 第几班
from tb_Data
order by inserttime
select realdata,inserttime,
case when datepart(hh,inserttime) between 7 and 15 then '第一班'
when datepart(hh,inserttime) between 15 and 23 then '第一班'
else '第三班' end as '班次'
from tb_Data
order by inserttime/*
realdata inserttime 班次
----------- ----------------------- ------
123 2014-04-25 07:12:00.000 第一班
123 2014-04-25 07:30:00.000 第一班
123 2014-04-25 14:58:00.000 第一班
123 2014-04-25 15:12:00.000 第一班
123 2014-04-25 22:12:00.000 第一班
123 2014-04-25 23:12:00.000 第一班
121 2014-04-26 06:12:00.000 第三班
*/楼主是要这样的结果吗?
select realdata,inserttime,
case when datepart(hh,inserttime) between 7 and 15 then '第一班'
when datepart(hh,inserttime) between 15 and 23 then '第二班'
else '第三班' end as '班次'
from tb_Data
order by inserttime/*
realdata inserttime 班次
----------- ----------------------- ------
123 2014-04-25 07:12:00.000 第一班
123 2014-04-25 07:30:00.000 第一班
123 2014-04-25 14:58:00.000 第一班
123 2014-04-25 15:12:00.000 第一班
123 2014-04-25 22:12:00.000 第二班
123 2014-04-25 23:12:00.000 第二班
121 2014-04-26 06:12:00.000 第三班
*/修改下