如果你的MenuID是整数类型的化,使用下面的:select * from tablename order by cast(MenuID as varchar(10))
select * from tablename order by case when ParentID is null then 1 else 0 end ,MenuID
--生成数据 reate table #t(MenuID int,ParentID int,MenuName varchar(40)) insert into #t select 1 ,NULL,rtrim('数据库 ') insert into #t select 2 ,NULL,rtrim('程序设计 ') insert into #t select 3 ,NULL,rtrim('网络技术 ') insert into #t select 11 ,1 ,rtrim('MS Server ') insert into #t select 12 ,1 ,rtrim('DB2 ') insert into #t select 13 ,1 ,rtrim('Oracle ') insert into #t select 14 ,3 ,rtrim('TCP/IP ') insert into #t select 15 ,3 ,rtrim('OSI ') insert into #t select 111,11 ,rtrim('MS Server书1') insert into #t select 112,11 ,rtrim('MS Server书2') insert into #t select 113,13 ,rtrim('Oracle书1 ') insert into #t select 114,15 ,rtrim('OSI书1 ') insert into #t select 115,15 ,rtrim('OSI书2 ') --执行查询 SELECT a.* FROM #t a LEFT JOIN #t b ON a.ParentID=b.MenuID ORDER BY CASE WHEN a.ParentID IS NULL THEN a.MenuID WHEN b.ParentID IS NULL THEN a.ParentID WHEN EXISTS(SELECT 1 FROM #t WHERE MenuID=b.ParentID AND ParentID IS NOT NULL) THEN (SELECT ParentID FROM #t WHERE MenuID=b.ParentID) ELSE b.ParentID END, CASE WHEN b.ParentID is null THEN a.MenuID ELSE a.ParentID END, a.MenuID --输出结果 MenuID ParentID MenuName ------ -------- ------------ 1 NULL 数据库 11 1 MS Server 111 11 MS Server书1 112 11 MS Server书2 12 1 DB2 13 1 Oracle 113 13 Oracle书1 2 NULL 程序设计 3 NULL 网络技术 14 3 TCP/IP 15 3 OSI 114 15 OSI书1 115 15 OSI书2
reate table #t(MenuID int,ParentID int,MenuName varchar(40))
insert into #t select 1 ,NULL,rtrim('数据库 ')
insert into #t select 2 ,NULL,rtrim('程序设计 ')
insert into #t select 3 ,NULL,rtrim('网络技术 ')
insert into #t select 11 ,1 ,rtrim('MS Server ')
insert into #t select 12 ,1 ,rtrim('DB2 ')
insert into #t select 13 ,1 ,rtrim('Oracle ')
insert into #t select 14 ,3 ,rtrim('TCP/IP ')
insert into #t select 15 ,3 ,rtrim('OSI ')
insert into #t select 111,11 ,rtrim('MS Server书1')
insert into #t select 112,11 ,rtrim('MS Server书2')
insert into #t select 113,13 ,rtrim('Oracle书1 ')
insert into #t select 114,15 ,rtrim('OSI书1 ')
insert into #t select 115,15 ,rtrim('OSI书2 ')
--执行查询
SELECT
a.*
FROM
#t a
LEFT JOIN
#t b
ON
a.ParentID=b.MenuID
ORDER BY
CASE
WHEN a.ParentID IS NULL THEN a.MenuID
WHEN b.ParentID IS NULL THEN a.ParentID
WHEN EXISTS(SELECT 1 FROM #t WHERE MenuID=b.ParentID AND ParentID IS NOT NULL) THEN (SELECT ParentID FROM #t WHERE MenuID=b.ParentID)
ELSE b.ParentID END,
CASE
WHEN b.ParentID is null THEN a.MenuID
ELSE a.ParentID END,
a.MenuID
--输出结果
MenuID ParentID MenuName
------ -------- ------------
1 NULL 数据库
11 1 MS Server
111 11 MS Server书1
112 11 MS Server书2
12 1 DB2
13 1 Oracle
113 13 Oracle书1
2 NULL 程序设计
3 NULL 网络技术
14 3 TCP/IP
15 3 OSI
114 15 OSI书1
115 15 OSI书2