declare @User table (userid int,state int,time numeric(2,1))
insert into @User
select 1,2,2.3 union all
select 2,2,2.4 union all
select 3,1,2.5 union all
select 4,2,3.5 union all
select 5,1,3.5 union all
select 6,0,3.6

declare @nfo table (id int,userid int,createtime numeric(2,1))
insert into @nfo
select 1,1,2.2 union all
select 2,1,2.3 union all
select 3,1,2.4 union all
select 4,2,2.4 union all
select 5,2,3.3 union all
select 6,4,2.5 union all
select 7,4,3.2

select  a.userid ,state ,time ,createtime
from @user a left join 
(
select  userid ,max(createtime) as createtime from  @nfo group by userid 
) b on a.userid = b.userid
order by case state when 2 then 1 else 0 end desc ,createtime desc ,time DESC
/*
userid      state       time                                    createtime
----------- ----------- --------------------------------------- ---------------------------------------
2           2           2.4                                     3.3
4           2           3.5                                     3.2
1           2           2.3                                     2.4
6           0           3.6                                     NULL
5           1           3.5                                     NULL
3           1           2.5                                     NULL
*/

------解决方案--------------------
SQL code

--刚才结果不对，修正一下
declare @User table (userid int,state int,time numeric(2,1))
insert into @User
select 1,2,2.3 union all
select 2,2,2.4 union all
select 3,1,2.5 union all
select 4,2,3.5 union all
select 5,1,3.5 union all
select 6,0,3.6

declare @nfo table (id int,userid int,createtime numeric(2,1))
insert into @nfo
select 1,1,2.2 union all
select 2,1,2.3 union all
select 3,1,2.4 union all
select 4,2,2.4 union all
select 5,2,3.3 union all
select 6,4,2.5 union all
select 7,4,3.2

select  a.userid ,state ,time ,createtime
from @user a left join 
(
select  userid ,max(createtime) as createtime from  @nfo group by userid 
) b on a.userid = b.userid
order by state desc ,createtime desc ,time DESC
/*
userid      state       time                                    createtime
----------- ----------- --------------------------------------- ---------------------------------------
2           2           2.4                                     3.3
4           2           3.5                                     3.2
1           2           2.3                                     2.4
5           1           3.5                                     NULL
3           1           2.5                                     NULL
6           0           3.6                                     NULL
*/