数据表结构如下:CTable id name parent_id order_no
------------------------------------------------------------ 1 MS-SQL Server 0 1
2 开发语言 0 2
3 硬件/嵌入开发 0 3
4 基础类 1 1
5 应用实例 1 2
6 VC/MFC 2 1
7 VB 2 2
8 汇编语言 3 1
9 界面 6 1怎么能用简单的SQL查询,不要用存储过程、创建临时表等,检索出如下效果 id name parent_id order_no
------------------------------------------------------------ 1 MS-SQL Server 0 1
4 基础类 1 1
5 应用实例 1 2
2 开发语言 0 2
6 VC/MFC 2 1
9 界面 6 1
7 VB 2 2
3 硬件/嵌入开发 0 3
8 汇编语言 3 1 就是按照CSDN左侧菜单的显示顺序,菜单级数不定
------------------------------------------------------------ 1 MS-SQL Server 0 1
2 开发语言 0 2
3 硬件/嵌入开发 0 3
4 基础类 1 1
5 应用实例 1 2
6 VC/MFC 2 1
7 VB 2 2
8 汇编语言 3 1
9 界面 6 1怎么能用简单的SQL查询,不要用存储过程、创建临时表等,检索出如下效果 id name parent_id order_no
------------------------------------------------------------ 1 MS-SQL Server 0 1
4 基础类 1 1
5 应用实例 1 2
2 开发语言 0 2
6 VC/MFC 2 1
9 界面 6 1
7 VB 2 2
3 硬件/嵌入开发 0 3
8 汇编语言 3 1 就是按照CSDN左侧菜单的显示顺序,菜单级数不定
BOM按节点排序应用实例
---------------------------------------------------------------------------- Author : htl258(Tony)-- Date : 2010-04-23 02:37:28-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)-- Subject: BOM按节点排序应用实例-------------------------------------------------------------------------- --实例1:--> 生成测试数据表:tb IF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb]GOCREATE TABLE [tb]([id] INT,[code] NVARCHAR(10),[pid] INT,[name] NVARCHAR(10))INSERT [tb]SELECT 1,'01',0,N'服装' UNION ALLSELECT 2,'01',1,N'男装' UNION ALLSELECT 3,'01',2,N'西装' UNION ALLSELECT 4,'01',3,N'全毛' UNION ALLSELECT 5,'02',3,N'化纤' UNION ALLSELECT 6,'02',2,N'休闲装' UNION ALLSELECT 7,'02',1,N'女装' UNION ALLSELECT 8,'01',7,N'套装' UNION ALLSELECT 9,'02',7,N'职业装' UNION ALLSELECT 10,'03',7,N'休闲装' UNION ALLSELECT 11,'04',7,N'西装' UNION ALLSELECT 12,'01',11,N'全毛' UNION ALLSELECT 13,'02',11,N'化纤' UNION ALLSELECT 14,'05',7,N'休闲装'GO--SELECT * FROM [tb] -->SQL查询如下: ;WITH T AS( SELECT CAST(CODE AS VARCHAR(20)) AS CODE,*, CAST(ID AS VARBINARY(MAX)) AS px FROM tb AS A WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.pid) UNION ALL SELECT CAST(B.CODE+A.CODE AS VARCHAR(20)),A.*, CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX)) FROM tb AS A JOIN T AS B ON A.pid=B.id)SELECT Code,Name FROM T ORDER BY px/*Code Name-------------------- ----------01 服装0101 男装010101 西装01010101 全毛01010102 化纤010102 休闲装0102 女装010201 套装010202 职业装010203 休闲装010204 西装01020401 全毛01020402 化纤010205 休闲装 (14 行受影响)*/ --实例2:--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[parentid] INT,[categoryname] NVARCHAR(10))
INSERT [tb]
SELECT 1,0,'test1' UNION ALL
SELECT 2,0,'test2' UNION ALL
SELECT 3,1,'test1.1' UNION ALL
SELECT 4,2,'test2.1' UNION ALL
SELECT 5,3,'test1.1.1' UNION ALL
SELECT 6,1,'test1.2'
GO
--SELECT * FROM [tb]-->SQL查询如下:
;WITH T AS
(
SELECT *,CAST(ID AS VARBINARY(MAX)) AS px
FROM tb AS A
WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.[parentid])
UNION ALL
SELECT A.*,CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX))
FROM tb AS A
JOIN T AS B
ON A.[parentid]=B.id
)
SELECT [id],[parentid],[categoryname] FROM T
ORDER BY px
/*
id parentid categoryname
----------- ----------- ------------
1 0 test1
3 1 test1.1
5 3 test1.1.1
6 1 test1.2
2 0 test2
4 2 test2.1(6 行受影响)
*/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/htl258/archive/2010/04/23/5518166.aspx
select '开发语言', 0 ,2 union all
select '硬件/嵌入开发', 0,3 union all
select '基础类', 1 ,1 union all
select '应用实例', 1, 2 union all
select 'VC/MFC', 2 ,1 union all
select 'VB', 2 ,2 union all
select '汇编语言', 3 ,1 union all
select ' 界面', 6 ,1
;WITH CET AS
(
SELECT *,CAST(ID AS VARBINARY(MAX)) AS RN
FROM tb AS A
WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.parent_id)
UNION ALL
SELECT A.*,CAST(B.RN+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX))
FROM tb AS A
JOIN CET AS B
ON A.[parent_id]=B.id
)
SELECT id ,name ,parent_id,order_no FROM CET
ORDER BY RN
/*
id name parent_id order_no
1 MS-SQL Server 0 1
4 基础类 1 1
5 应用实例 1 2
2 开发语言 0 2
6 VC/MFC 2 1
9 界面 6 1
7 VB 2 2
3 硬件/嵌入开发 0 3
8 汇编语言 3 1
*/
--> 测试数据:[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table tbl(
[id] int,
[name] varchar(13),
[parent_id] varchar(6),
[order_no] int
)
insert tbl
select 1,'MS-SQL Server',0,1 union all
select 2,'开发语言',0,2 union all
select 3,'硬件/嵌入开发',0,3 union all
select 4,'基础类',1,1 union all
select 5,'应用实例',1,2 union all
select 6,'VC/MFC',2,1 union all
select 7,'VB',2,2 union all
select 8,'汇编语言',3,1 union all
select 9,'界面',6,1
;WITH T AS(
SELECT *,CAST(ID AS VARBINARY(MAX)) AS px FROM [tbl] AS A WHERE NOT EXISTS(SELECT * FROM [tbl] WHERE id=A.[parent_id])
UNION ALL
SELECT A.*,
CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX)) FROM [tbl] AS A JOIN T AS B ON A.[parent_id]=B.id)SELECT id,name,parent_id,order_no FROM T
ORDER BY px/*
id name parent_id order_no
1 MS-SQL Server 0 1
4 基础类 1 1
5 应用实例 1 2
2 开发语言 0 2
6 VC/MFC 2 1
9 界面 6 1
7 VB 2 2
3 硬件/嵌入开发 0 3
8 汇编语言 3 1
*/
--这网速太坑爹了
select * from CTable
order by parent_id,order_no