SQL语句(排序)

xiaoyuppou 2008-03-11 09:56:18
排序问题
表如下
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
...全文
129 点赞 收藏 11
写回复
11 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
yesyesyes 2008-03-11
select id,time
from tableName
order by
CASE when time>GETDATE()
THEN time
ELSE -1*cast(time as int)
END
回复
lhsxsh 2008-03-11
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


回复
neterx 2008-03-11
顶下
回复
seemon 2008-03-11

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
回复
pt1314917 2008-03-11
两个查询结果进行union 或者union all时,不能排序。 。。
回复
-狙击手- 2008-03-11
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 行)
*/
回复
-狙击手- 2008-03-11
如下解决方法为什么不对?提示 在关键字 '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 去掉
回复
wsklt 2008-03-11
select id,time
from tableName
where time> getdate()
union
select id,time
from tableName
where time <getdate()
order by time
只需要一個order by
回复
dawugui 2008-03-11
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 行)
*/
回复
kofoun 2008-03-11

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
)
回复
yesyesyes 2008-03-11
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
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-11 09:56
社区公告
暂无公告