sql查询的问题？

[gaopei1122]

表结构
a b
1 5
0 2
1 4
1 7
0 11
如上表，我想查询的结果是，先把a=1的列安b的降序排列，后面在把a<>1的列安b降序排列，
这个语句改怎么写呀

[-狙击手-]

select *
from t
order by case when a = 1 then 9999999 - b else b end desc

[ORARichard]

select * from tb order by -abs(1-a),b desc

[-狙击手-]

看错了

declare @t table(a int,b int)
insert @t select
1, 5 union select
0, 2 union select
1 , 4 union select
1 , 7 union select
0 , 11

select *
from @t
order by case when a = 1 then  0 else 1 end ,b desc

/*


a           b           
----------- ----------- 
1           7
1           5
1           4
0           11
0           2

（所影响的行数为 5 行）

*/

[kk19840210]

declare @t table (a int,b int)
insert into @t values(1,5)
insert into @t values(0,2)
insert into @t values(1,4)
insert into @t values(1,7)
insert into @t values(0,11)

select * from
(
select top 100 PERCENT * from @t where a=1 order by b desc
) a
union all
select * from
(
select top 100 PERCENT * from @t where a<>1 order by b 
) b


a           b
----------- -----------
1           5
1           4
1           7
0           2
0           11

(5 行受影响)

[-狙击手-]

先把a=1的列安b的降序排列，后面在把a <> 1的列安b降序排列，

－－－

B　全降序　，　我还以为是a = 1　时升，＜＞　1　时降呢

[ORARichard]

select   *   from   tb   order   by   abs(1-a),b   desc --不用那个负号

[-狙击手-]

declare @t table(a int,b int)
insert @t select 
1,     5 union select 
0,     2 union select 
1 ,    4 union select 
1   ,  7 union select 
0    , 11 

-- a=1 asc ,a<> 1 desc
select *
from @t
order by case when a = 1 then  -b else b end desc

/*
a           b           
----------- ----------- 
0           11
0           2
1           4
1           5
1           7

（所影响的行数为 5 行）
*/

[tre_sdlpq]

表结构
a b
1 5
0 2
1 4
1 7
0 11



select * from 表
where a=1
order by b desc

union
select * from 表
where a <> 1
order by b desc

[yangjiexi]

declare @tb table(a int,b int)
insert into @tb
select 1,5 
union
select 0,2 
union
select 1,4 
union
select 1,7 
union
select 0,11

select * from @tb
order by case when a=1 then 0 else 1 end,b desc

[gaopei1122]

好的，明白了，谢谢