一个稍复杂的sql排序(附图)

Question

--这样？
select * from info
order by 
case when datediff(d,publictime,getdate())>0 then 1 else 0 end,grade desc,publictime desc

Answer 1

这是一个简单的会员发布信息表，title-信息标题 member-会员号 grade-会员等级 publictime-发布时间 
(假设今天是:2008-10-13) 想要实现的是：
1.当天发布的信息会员等级高的排在前面（即按会员等级高低排列）,会员等级相同的再按发布时间倒序排列。
2.历史信息按会员的发布时间倒序进行排列。
图我发上去了,看不到看这里http://hiphotos.baidu.com/weasle/pic/item/f19bb238273aa6d8d462255f.jpg

Answer 2

select * from info
order by 
case when datediff(d,publictime,getdate())=0 then 0 else 1 end,grade desc,publictime desc 

Answer 3

结果不对,如下图:
2 标题2 b 3 2008-10-13 9:20:50
4 标题4 d 3 2008-10-13 8:10:50
8 标题8 h 2 2008-10-13 18:15:50
3 标题3 c 2 2008-10-13 10:25:50
1 标题1 a 1 2008-10-13 17:20:50
5 标题5 e 3 2008-10-12 10:15:50
7 标题7 g 2 2008-10-12 11:15:50
6 标题6 f 1 2008-10-12 13:20:50

Answer 4

SELECT *
FROM info
ORDER BY 
  CASE 
     WHEN DATEDIFF(DAY,publictime,GETDATE())=0
        THEN grade ELSE NULL END DESC,
  CASE 
     WHEN DATEDIFF(DAY,publictime,GETDATE())=0
        THEN publictime ELSE NULL END DESC,
  CASE 
     WHEN DATEDIFF(DAY,publictime,GETDATE())>0
        THEN publictime ELSE NULL END DESC--这样?

Answer 5

id     title                                                member   grade    publictime                       
----------- -------------------------------------------- ---------- ----------- -------------------------- 
2      标题2                                                 b     3      2008-10-13 09:20:50.000
4      标题4                                                 d     3      2008-10-13 08:10:50.000
8      标题8                                                 h     2      2008-10-13 18:15:50.000
3      标题3                                                 c     2      2008-10-13 10:25:50.000
1      标题1                                                 a     1      2008-10-13 17:20:50.000
6      标题6                                                 f     1      2008-10-12 13:20:50.000
7      标题7                                                 g     2      2008-10-12 11:15:50.000
5      标题5                                                 e     3      2008-10-12 10:15:50.000

Answer 6

晕。语句发漏了。。
select * from info
order by 
case when datediff(d,publictime,getdate())=0 then grade else null end desc,publictime desc

Answer 7

呵呵,谢谢大家,会了,liangCK ,pt1314917 都是对的.