急，全部家当 求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' 的那个节点是否是固定的   你要的那个排序结果有什么规则