求语句，SQL排序问题

[默清寒]

给table A里的数据排序，排序方式为：当字段B = b1时，按照 字段C asc，字段D asc排序，当字段B= b2时，按照 字段C desc,字段D desc排序，SQL语句应该怎么写？求助~~~

[Shawn]

这个楼主得给个数据及结果。

[默清寒]

引用 1 楼 wwwwgou 的回复:

这个楼主得给个数据及结果。

create table A ( id char(1), B char(1), C int, D int ) insert into A ('1','0',1,2) insert into A ('2','0',1,3) insert into A ('3','1',3,6) insert into A ('4','1',3,5) insert into A ('5','1',4,4) insert into A ('6','0',2,1) insert into A ('7','0',1,4) --------------------------------- 最后的结果应该是 当 B= 0时 排序是 1,2,7,6 当 B= 1时 排序是 5,3,4 全部在一起排列则是 1,2,7,6,5,3,4 需要用row_numer()函数来确定行号。谢谢

[Andy__Huang]

select *
from tb
order by case when B = b1 then C else 0 end,case when B = b1 then D else 0 end,
case when B = b2 then C else 0 end desc,case when B = b2 then D else 0 end desc

[Andy__Huang]

create table A (
   id char(1),
   B char(1),
   C int,
   D int
)
insert into A values('1','0',1,2)
insert into A values('2','0',1,3)
insert into A values('3','1',3,6)
insert into A values('4','1',3,5)
insert into A values('5','1',4,4)
insert into A values('6','0',2,1)
insert into A values('7','0',1,4)

select *
from A
order by B desc,case when B = 0 then C else 1 end,case when B = 0 then D else 1 end,
case when B = 1 then C else 0 end desc,case when B = 1 then D else 0 end desc

drop table A

/*
id 	B	C	D
-------------------------
5	1	4	4
3	1	3	6
4	1	3	5
1	0	1	2
2	0	1	3
7	0	1	4
6	0	2	1

*/

[Andy__Huang]

上面B的排序不对,修正:

select *
from A
order by B ,case when B = 0 then C else 1 end,case when B = 0 then D else 1 end,
case when B = 1 then C else 0 end desc,case when B = 1 then D else 0 end desc

drop table A

/*
id 	B	C	D
-------------------------
1	0	1	2
2	0	1	3
7	0	1	4
6	0	2	1
5	1	4	4
3	1	3	6
4	1	3	5

*/

[默清寒]

引用 5 楼 hdhai9451 的回复:

上面B的排序不对,修正:

select *
from A
order by B ,case when B = 0 then C else 1 end,case when B = 0 then D else 1 end,
case when B = 1 then C else 0 end desc,case when B = 1 then D else 0 end desc

drop table A

/*
id 	B	C	D
-------------------------
1	0	1	2
2	0	1	3
7	0	1	4
6	0	2	1
5	1	4	4
3	1	3	6
4	1	3	5

*/

非常感谢，就是这个结果