SQL语句（排序）

[xiaoyuppou]

排序问题
表如下
id time
1 2008-12-3
2 2007-11-2
3 2008-10-21
4 2008-5-1
5 2007-7-7
要求（如果时间大于当前时间，就按升序排，时间小于当前时间，就按降序排）
排列如下
4 2008-5-1
3 2008-10-21
1 2008-12-3
2 2007-11-2
5 2007-7-7
如下解决方法为什么不对？提示 在关键字 'union' 附近有语法错误。
select id,time
from tableName
where time>getdate()
order by time
union
select id,time
from tableName
where time<getdate()
order by time desc

[yesyesyes]

select id,time
from tableName
order by
CASE when time> GETDATE()
THEN 1
ELSE 2
END,
CASE when time> GETDATE()
THEN time
ELSE -1*cast(time as int)
END

[kofoun]

select * from (select top 50 * from tb where time >getdate() order by time)t union all
select * from (select top 50 from tb where time <getdate() order by time desc )t
)

[dawugui]

create table tb(id int, time datetime)
insert into tb values(1, '2008-12-3') 
insert into tb values(2, '2007-11-2') 
insert into tb values(3, '2008-10-21') 
insert into tb values(4, '2008-5-1') 
insert into tb values(5, '2007-7-7')
go

select * from (select top 100 * from tb where time > getdate() order by time) t
union all
select * from (select top 100 * from tb where time < getdate() order by time desc) t

drop table tb

/*
id          time                                                   
----------- ------------------------------------------------------ 
4           2008-05-01 00:00:00.000
3           2008-10-21 00:00:00.000
1           2008-12-03 00:00:00.000
2           2007-11-02 00:00:00.000
5           2007-07-07 00:00:00.000

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

[wsklt]

select id,time
from tableName
where time> getdate()
union
select id,time
from tableName
where time <getdate()
order by time
只需要一個order by

[-狙击手-]

如下解决方法为什么不对？提示 在关键字 'union' 附近有语法错误。
select id,time
from tableName
where time> getdate()
order by time
union
select id,time
from tableName
where time <getdate()
order by time desc

-----------------
光语法上 union 前的order 去掉

[-狙击手-]

declare @t table(id int,time datetime)
insert @t select
1 ,'2008-12-3' union select  
2 ,'2007-11-2' union select  
3 ,'2008-10-21' union select  
4 ,'2008-5-1' union select  
5 ,'2007-7-7'




select * from (select top 100 PERCENT * from @t where time > getdate() order by time) t
union all
select * from (select top 100 PERCENT * from @t where time < getdate() order by time desc) t


/*
id          time                                                   
----------- ------------------------------------------------------ 
1           2008-12-03 00:00:00.000
3           2008-10-21 00:00:00.000
4           2008-05-01 00:00:00.000
2           2007-11-02 00:00:00.000
5           2007-07-07 00:00:00.000

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

[pt1314917]

两个查询结果进行union 或者union all时，不能排序。 。。

[seemon]

select id,time 
from tableName 
order by 
CASE time>GETDATE() 
  THEN CONVERT( Float, time)-CONVERT( Float, GETDATE())+CONVERT(FLOAT,'2000-01-01')
  ELSE CONVERT( Float, GETDATE())-CONVERT( Float, time)
END

[neterx]

顶下

[lhsxsh]

declare @t table(id int,time datetime)
insert @t select
1 ,'2008-12-3' union select
2 ,'2007-11-2' union select
3 ,'2008-10-21' union select
4 ,'2008-5-1' union select
5 ,'2007-7-7'




select * from (select top 100 PERCENT * from @t where time > getdate() order by time) t
union all
select * from (select top 100 PERCENT * from @t where time < getdate() order by time desc) t

[yesyesyes]

select id,time
from tableName
order by
CASE when time>GETDATE()
THEN time
ELSE -1*cast(time as int)
END