select *
from (
select top 100 percent id,dorder
from test
where dorder<>0
order by dorder asc
)t
union
select id,dorder from test where dorder=0 order by id desc
from (
select top 100 percent id,dorder
from test
where dorder<>0
order by dorder asc
)t
union
select id,dorder from test where dorder=0 order by id desc
from (
select top 100 percent id,dorder
from test
where dorder<>0
order by dorder asc
)t
union all
select id,dorder from test where dorder=0 order by id desc
--------------union查出结果-------------
9 62
8 66
7 0
6 8
5 0
4 3
3 13
2 23
1 2
from (
select id,dorder from test where dorder<>0
union
select id,dorder from test where dorder=0
)t
order by dorder asc,
id desc
---------------------
7 0
5 0
1 2
4 3
6 8
3 13
2 23
9 62
8 66
from (
select id,dorder from test where dorder<>0
union
select id,dorder from test where dorder=0
)t
order by dorder desc,
id desc
----------------
8 66
9 62
2 23
3 13
6 8
4 3
1 2
7 0
5 0
---------------我想要的sort结果是----------------
1 2
4 3
6 8
3 13
2 23
9 62
8 66
7 0
5 0
(
id int,dorder int
)
insert @tb
select 8,66 union
select 9,62 union
select 2,23 union
select 3,13 union
select 6,8 union
select 4,3 union
select 1,2 union
select 7,0 union
select 5,0--查询
select * from @tb
order by (case when dorder=0 then 2 else 0 end)
,dorder --结果
/*
id dorder
----------- -----------
1 2
4 3
6 8
3 13
2 23
9 62
8 66
7 0
5 0
*/
id title dorder
1 fff 0
..............
像遇到这种就不行了.
29 3 1
30 44 12
31 4 0
34 234 0
最后显示成这种错误的呀
(case when dorder=0 then 2 else 0 end)这个干什么呀?
select id,title,dorder from TB_Information
order by (case when dorder=0 then 2 else 0 end),dorder,id desc
select A.*
from ( select id,dorder from test where dorder<>0
union
select id,dorder from test where dorder=0
) A
order by A.id desc