现有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*/