数据库如下：
id pid name
1 0 AAA
2 0 BBB
3 1 CCC
4 1 DDD
5 2 EEE
6 2 FFF
7 2 GGG
想要得到的结果：
id pid name
1 0 AAA
3 1 CCC
4 1 DDD
2 0 BBB
5 2 EEE
6 2 FFF
7 2 GGG
就是说，pid为 n 的行 跟在 id 为 n 的行后面，
期待高手出现
------解决方案--------------------
select a,b,c from (select d.*,case when b=0 then a else b end as d from (
select 1 as A, 0 as b,'AAA' as C union all
select 2 as A, 0 as b,'BBB' as C union all
select 3 as A, 1 as b,'CCC' as C union all
select 4 as A, 1 as b,'DDD' as C union all
select 5 as A, 2 as b,'EEE' as C union all
select 6 as A, 2 as b,'FFF' as C union all
select 7 as A, 2 as b,'GGG' as C )d)E order by d,a
------解决方案--------------------
- SQL code
DROP TABLE tb0CREATE TABLE tb0( id INT, pid INT, name VARCHAR(10))INSERT INTO tb0SELECT 1, 0, 'AAA' UNIONSELECT 2, 0, 'BBB' UNIONSELECT 3, 1, 'CCC' UNIONSELECT 4, 1, 'DDD' UNIONSELECT 5, 2, 'EEE' UNIONSELECT 6, 2, 'FFF' UNIONSELECT 7, 2, 'GGG'SELECT Id,Pid,nameFROM (SELECT CASE WHEN A.id = B.pid THEN B.id WHEN A.pid IS NULL THEN B.id ELSE A.pid END AS Id,CASE WHEN A.id IS NULL THEN B.id ELSE A.id END AS AId, B.pid,B.nameFROM tb0 as A RIGHT OUTER JOIN tb0 as B ON A.id = B.pid) AS AORDER BY AId,Id,pidId Pid name1 0 AAA3 1 CCC4 1 DDD2 0 BBB5 2 EEE6 2 FFF7 2 GGG
------解决方案--------------------
Declare @T Table (
id Int,
pid Int,
name varchar(20)
)
Insert Into @T
Select 1, 0, 'AAA'
Union Select 2, 0, 'BBB'
Union Select 3, 1, 'CCC'
Union Select 4, 1, 'DDD'
Union Select 5, 2, 'EEE'
Union Select 6, 2, 'FFF'
Union Select 7, 2, 'GGG'
Select * From @T
Order By case when Pid=0 then id else pid End,id
- SQL code