MS-sql中排序问题
现有2个表
User
userid ，state，time
1 2 2.3
2 2 2.4
3 1 2.5
4 2 3.5
5 1 3.5
6 0 3.6
nfo
id，userid ，createtime
1 1 2.2
2 1 2.3
3 1 2.4 *
4 2 2.4
5 2 3.3 *
6 4 2.5
7 4 3.2 *

state 有 2，1，0 三种状态

现要根据state 和 createtime 排序
先按state降序排，
接着state为2的按createtime降序排
1，0 的按time降序排

结果应为
userid
2
4
1
5
3
6

怎么写



[解决办法]

SQL code

declare @User table (userid int,state int,time numeric(2,1))insert into @Userselect 1,2,2.3 union allselect 2,2,2.4 union allselect 3,1,2.5 union allselect 4,2,3.5 union allselect 5,1,3.5 union allselect 6,0,3.6declare @nfo table (id int,userid int,createtime numeric(2,1))insert into @nfoselect 1,1,2.2 union allselect 2,1,2.3 union allselect 3,1,2.4 union allselect 4,2,2.4 union allselect 5,2,3.3 union allselect 6,4,2.5 union allselect 7,4,3.2select  a.userid ,state ,time ,createtimefrom @user a left join (select  userid ,max(createtime) as createtime from  @nfo group by userid ) b on a.userid = b.useridorder by case state when 2 then 1 else 0 end desc ,createtime desc ,time DESC/*userid      state       time                                    createtime----------- ----------- --------------------------------------- ---------------------------------------2           2           2.4                                     3.34           2           3.5                                     3.21           2           2.3                                     2.46           0           3.6                                     NULL5           1           3.5                                     NULL3           1           2.5                                     NULL*/
[解决办法]
SQL code
--刚才结果不对，修正一下declare @User table (userid int,state int,time numeric(2,1))insert into @Userselect 1,2,2.3 union allselect 2,2,2.4 union allselect 3,1,2.5 union allselect 4,2,3.5 union allselect 5,1,3.5 union allselect 6,0,3.6declare @nfo table (id int,userid int,createtime numeric(2,1))insert into @nfoselect 1,1,2.2 union allselect 2,1,2.3 union allselect 3,1,2.4 union allselect 4,2,2.4 union allselect 5,2,3.3 union allselect 6,4,2.5 union allselect 7,4,3.2select  a.userid ,state ,time ,createtimefrom @user a left join (select  userid ,max(createtime) as createtime from  @nfo group by userid ) b on a.userid = b.useridorder by state desc ,createtime desc ,time DESC/*userid      state       time                                    createtime----------- ----------- --------------------------------------- ---------------------------------------2           2           2.4                                     3.34           2           3.5                                     3.21           2           2.3                                     2.45           1           3.5                                     NULL3           1           2.5                                     NULL6           0           3.6                                     NULL*/