用一个SQL语句不知道能否实现这个功能？

[yifanwu]

一个表：
id parent_id value
1 0 0
2 0 0
3 1 1
4 2 1
5 1 2

要求输出：

id
1
5
3
2
4

就是先输出ID，再输出按value排序的parent_id=id的行。


谢谢。

[jaydom]

看不懂规律，只能帮顶了

[Sharon_liu]

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(id INT, parent_id INT, value INT)
INSERT INTO TB
SELECT 1, 0, 0 UNION ALL
SELECT 2, 0, 0 UNION ALL
SELECT 3, 1, 1 UNION ALL
SELECT 4, 2, 1 UNION ALL
SELECT 5, 1, 2
;WITH MU AS (
SELECT 'LEVEL'=1,NID=ID,* FROM TB WHERE parent_id=0
UNION ALL
SELECT [LEVEL]+1,NID,TB.*
FROM TB
INNER JOIN MU ON TB.parent_id =MU.id 
)
SELECT ID FROM MU
ORDER BY NID,LEVEL ASC,value DESC
/*
1
5
3
2
4
*/

[yifanwu]

谢谢Sharon_liu的方法，但是有不用太复杂的办法么？

因为希望能在各种数据库平台上都能实现。

[htl258_Tony]

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
	DROP TABLE [tb]
GO
CREATE TABLE [tb] ([id] [int],[parent_id] [int],[value] [int])
INSERT INTO [tb]
SELECT '1','0','0' UNION ALL
SELECT '2','0','0' UNION ALL
SELECT '3','1','1' UNION ALL
SELECT '4','2','1' UNION ALL
SELECT '5','1','2'

--SELECT * FROM [tb]

-->SQL查询如下:
;WITH t AS 
(
    SELECT *, px = CAST(ROW_NUMBER()OVER(ORDER BY VALUE DESC) AS VARBINARY) 
    FROM tb
    WHERE parent_id = 0
    UNION ALL
    SELECT a.*, CAST(
               px+CAST(
                   ROW_NUMBER()OVER(PARTITION BY a.parent_id ORDER BY a.VALUE DESC) AS VARBINARY
               ) AS VARBINARY
           ) 
    FROM tb a, t b
    WHERE a.parent_id = b.id
)
SELECT id 
FROM t
ORDER BY px
/*
id
-----------
1
5
3
2
4

(5 行受影响)
*/

[jianuMan]

看不懂规律

楼主说清楚点

[304的的哥]

学习了！

[永生天地]

[Quote=引用 3 楼 yifanwu 的回复:]
谢谢Sharon_liu的方法，但是有不用太复杂的办法么？

因为希望能在各种数据库平台上都能实现。
[/Quote]

如果数据库支持写函数，看看这个函数的做法
http://blog.csdn.net/xys_777/archive/2010/06/15/5672481.aspx