请求大家帮助
表patriot_comments ,想要根据topCommit_time,floor两列查询,如果用这种排序
select * from (select * from patriot_comments where topcommit_time>sysdate union select * from patriot_comments) order by topCommit_time desc,floor desc
的话查询的结果是先根据topcommit_time倒序,然后再根据floor倒序,()现在我想要既topcommit_time倒序的同时floor也倒序,这语句可该怎么写呢
现今查询的结果是
我想要的结果是
floor topcommit_time
5 07/02/2011 00:00:00
4 01/20/2011 00:00:00
18 11/11/1111 00:00:00
17 11/11/1111 00:00:00
16 11/11/1111 00:00:00
15 11/11/1111 00:00:00
9 04/20/2010 00:00:00
意思就是大于当前时间的按时间倒序排在前面,小于当前时间的按floor倒序排在后面
表patriot_comments ,想要根据topCommit_time,floor两列查询,如果用这种排序
select * from (select * from patriot_comments where topcommit_time>sysdate union select * from patriot_comments) order by topCommit_time desc,floor desc
的话查询的结果是先根据topcommit_time倒序,然后再根据floor倒序,()现在我想要既topcommit_time倒序的同时floor也倒序,这语句可该怎么写呢
现今查询的结果是
我想要的结果是
floor topcommit_time
5 07/02/2011 00:00:00
4 01/20/2011 00:00:00
18 11/11/1111 00:00:00
17 11/11/1111 00:00:00
16 11/11/1111 00:00:00
15 11/11/1111 00:00:00
9 04/20/2010 00:00:00
意思就是大于当前时间的按时间倒序排在前面,小于当前时间的按floor倒序排在后面
select * from patriot_comments
order by sign(topcommit_time-sysdate),floor desc
from (select r.*,
case
when topCommit_time <= sysdate then
1
else
0
end flag
from patriot_comments r)
order by flag,topCommit_time desc, floor desc
非常好,谢谢,去掉中间这个 topCommit_time desc,就ok啦