啥数据库:SqlServer?
----------
with test as(
select '2016-03-16 09:09:48.383' [time_]
union all
select '2017-03-12 22:20:29.037'
union all
select '2016-03-14 21:52:16.160'
union all
select '2016-03-17 09:12:12.843'
union all
select '2016-03-17 09:10:12.843'
union all
select '2016-03-14 21:52:16.160'
union all
select '2016-03-17 08:12:12.843'
union all
select '2016-03-17 07:12:12.843'
union all
select '2016-03-17 15:24:00.070'
union all
select '2016-03-17 15:24:00.070'
union all
select '2016-03-14 21:52:16.160'
union all
select '2016-03-17 15:24:00.070'
)
select *,(CASE WHEN time_>=CONCAT(CONVERT(NVARCHAR(10),time_,120),' 20:30:00') AND time_<=CONCAT(CONVERT(NVARCHAR(10),DATEADD(DAY,1,time_),120),' 20:30:00') THEN '中班'
WHEN time_>=CONCAT(CONVERT(NVARCHAR(10),time_,120),' 03:30:00') AND time_<=CONCAT(CONVERT(NVARCHAR(10),time_,120),' 07:00:00') THEN '小夜班'
WHEN time_>=CONCAT(CONVERT(NVARCHAR(10),time_,120),' 07:00:00') AND time_<=CONCAT(CONVERT(NVARCHAR(10),time_,120),' 09:30:00') THEN '大夜'
ELSE '未分类' END ) [TYPE_] from test