急，全部家当 求SQL 语句

Question

IF OBJECT_ID('[Navigation]') IS NOT NULL DROP TABLE [Navigation]
GO

CREATE TABLE Navigation
(
    NavID INT PRIMARY KEY NOT NULL,
    NavName VARCHAR(50),
    ParentID int  --为0表示顶级节点
)

INSERT Navigation
SELECT 1,'节点1',0 UNION ALL
SELECT 2,'节点2',0 UNION ALL
SELECT 3,'节点3',0 UNION ALL
SELECT 4,'节点1-1',1 UNION ALL
SELECT 5,'节点4',0 UNION ALL
SELECT 6,'节点3-1',3 UNION ALL
SELECT 7,'节点3-2',3 UNION ALL
SELECT 8,'节点4-1',5 UNION ALL
SELECT 9,'节点1-2',1 UNION ALL
SELECT 10,'节点2-1',2
go

SELECT * FROM Navigation
/*
如何得到如下结果(最好别用存储过程)
NavID     NavName   ParentID
1        节点1     0
4        节点1-1   1
9        节点1-2   1
2        节点2     0
10       节点2-1   2
3        节点3     0
6        节点3-1   3
7        节点3-2   3
5        节点4     0
8        节点4-1   5
*/

Answer 1

select NavID,NavName ,ParentID  from (select NavID,NavName ,ParentID, (case when ParentID = 0 then NavID else ParentID end) as pid  from Navigation) a order by pid,navid

Answer 2

SELECT * FROM Navigation order by NavName

Answer 3

请问你上面所谓的 '节点1' 的那个节点是否是固定的   你要的那个排序结果有什么规则