表有3个字段,
name, D_datetime, TF
xm1 2010-10-26 08:28:00 True
xm2 2010-10-26 08:33:30 False
xm3 2010-10-26 08:56:07 False
xm4 2010-10-26 10:44:09 True
xm5 2010-10-26 13:26:47 False
xm6 2010-10-26 14:21:32 True
xm7 2010-10-26 14:51:37 False
xm8 2010-10-26 15:43:13 False要求:分上午按TF排序+下午按TF排序,结果如下:
xm2 2010-10-26 08:33:30 False
xm3 2010-10-26 08:56:07 False
xm1 2010-10-26 08:28:00 True
xm4 2010-10-26 10:44:09 True
xm5 2010-10-26 13:26:47 False
xm7 2010-10-26 14:51:37 False
xm8 2010-10-26 15:43:13 False
xm6 2010-10-26 14:21:32 True请教:如何实现?
我下面的语句无法实现:select * from (select * table where (D_datetime>='2010-10-26 00:00:00' and D_datetime<='2010-10-26 11:59:59' ) order by TF) t1
union
select * from (select * table where (D_datetime>='2010-10-26 00:00:00' and D_datetime<='2010-10-26 11:59:59' ) order by TF) t2
name, D_datetime, TF
xm1 2010-10-26 08:28:00 True
xm2 2010-10-26 08:33:30 False
xm3 2010-10-26 08:56:07 False
xm4 2010-10-26 10:44:09 True
xm5 2010-10-26 13:26:47 False
xm6 2010-10-26 14:21:32 True
xm7 2010-10-26 14:51:37 False
xm8 2010-10-26 15:43:13 False要求:分上午按TF排序+下午按TF排序,结果如下:
xm2 2010-10-26 08:33:30 False
xm3 2010-10-26 08:56:07 False
xm1 2010-10-26 08:28:00 True
xm4 2010-10-26 10:44:09 True
xm5 2010-10-26 13:26:47 False
xm7 2010-10-26 14:51:37 False
xm8 2010-10-26 15:43:13 False
xm6 2010-10-26 14:21:32 True请教:如何实现?
我下面的语句无法实现:select * from (select * table where (D_datetime>='2010-10-26 00:00:00' and D_datetime<='2010-10-26 11:59:59' ) order by TF) t1
union
select * from (select * table where (D_datetime>='2010-10-26 00:00:00' and D_datetime<='2010-10-26 11:59:59' ) order by TF) t2
select a.*, case when tf = false then 0
when tf = true then 1
else 3 end as tf_num
from table a where hour(d_datetime)<=11
union all
select a.*, case when tf = false then 1
when tf = true then 0
else 3 end as tf_num
from table a where hour(d_datetime)>=12
) order by d_datetime,tf_num
insert into tb select 'xm1','2010-10-26 08:28:00','True'
insert into tb select 'xm2','2010-10-26 08:33:30','False'
insert into tb select 'xm3','2010-10-26 08:56:07','False'
insert into tb select 'xm4','2010-10-26 10:44:09','True'
insert into tb select 'xm5','2010-10-26 13:26:47','False'
insert into tb select 'xm6','2010-10-26 14:21:32','True'
insert into tb select 'xm7','2010-10-26 14:51:37','False'
insert into tb select 'xm8','2010-10-26 15:43:13','False'
go
select * from tb order by
(case when datepart(hh,d_datetime)/12=0 then (case when tf='True' then 1 else 0 end) else (case when tf='True' then 0 else 1 end) end)
go
drop table tb
/*
name D_datetime TF
---------- ----------------------- ----------
xm2 2010-10-26 08:33:30.000 False
xm3 2010-10-26 08:56:07.000 False
xm6 2010-10-26 14:21:32.000 True
xm7 2010-10-26 14:51:37.000 False
xm8 2010-10-26 15:43:13.000 False
xm1 2010-10-26 08:28:00.000 True
xm4 2010-10-26 10:44:09.000 True
xm5 2010-10-26 13:26:47.000 False(8 行受影响)*/
create table tb(name nvarchar(10),D_datetime datetime,TF nvarchar(10))
insert into tb select 'xm1','2010-10-26 08:28:00','True'
insert into tb select 'xm2','2010-10-26 08:33:30','False'
insert into tb select 'xm3','2010-10-26 08:56:07','False'
insert into tb select 'xm4','2010-10-26 10:44:09','True'
insert into tb select 'xm5','2010-10-26 13:26:47','False'
insert into tb select 'xm6','2010-10-26 14:21:32','True'
insert into tb select 'xm7','2010-10-26 14:51:37','False'
insert into tb select 'xm8','2010-10-26 15:43:13','False'
go
select * from tb order by datepart(hh,d_datetime)/12,tf
go
drop table tb
/*
name D_datetime TF
---------- ----------------------- ----------
xm2 2010-10-26 08:33:30.000 False
xm3 2010-10-26 08:56:07.000 False
xm4 2010-10-26 10:44:09.000 True
xm1 2010-10-26 08:28:00.000 True
xm7 2010-10-26 14:51:37.000 False
xm8 2010-10-26 15:43:13.000 False
xm5 2010-10-26 13:26:47.000 False
xm6 2010-10-26 14:21:32.000 True(8 行受影响)*/
select * from tb order by datepart(hour,D_datetime),tf