求一句sql

如果只是分级 parentID 字段只是类似会员卡之类的普通会员金卡会员银卡会员
没有从属关系
而用 code字段就有从属关系

通过父节点，完全可以显示出一颗树，只是我写的sql比较复杂，希望高手给个简洁的

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

/*
标题：SQL SERVER 2005中树结构按照各顶级节点排序
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)
时间：2010-10-24
地点：陕西西安
说明：
如下树级结构中需要按照各顶级节点排序
id   pid  name
---- ---- ----------
001  000  广东省
002  001  广州市
003  001  深圳市
004  002  天河区
005  003  罗湖区
006  003  福田区
007  003  宝安区
008  007  西乡镇
009  007  龙华镇
010  007  松岗镇
011  000  江西省
012  011  南昌市
013  012  南昌县
014  012  新建县
015  012  进贤县
016  012  安义县
017  013  麻丘镇
018  011  九江市
需要的结果：
id   name       pid
---- ---------- ----
001  广东省     000
002  广州市     001
004  天河区     002
003  深圳市     001
005  罗湖区     003
006  福田区     003
007  宝安区     003
008  西乡镇     007
009  龙华镇     007
010  松岗镇     007
011  江西省     000
012  南昌市     011
013  南昌县     012
017  麻丘镇     013
014  新建县     012
015  进贤县     012
016  安义县     012
018  九江市     011
*/create table tb(id varchar(3) , pid varchar(3) , name nvarchar(10))
insert into tb values('001' , '000' , N'广东省')
insert into tb values('002' , '001' , N'广州市')
insert into tb values('003' , '001' , N'深圳市')
insert into tb values('004' , '002' , N'天河区')
insert into tb values('005' , '003' , N'罗湖区')
insert into tb values('006' , '003' , N'福田区')
insert into tb values('007' , '003' , N'宝安区')
insert into tb values('008' , '007' , N'西乡镇')
insert into tb values('009' , '007' , N'龙华镇')
insert into tb values('010' , '007' , N'松岗镇')
insert into tb values('011' , '000' , N'江西省')
insert into tb values('012' , '011' , N'南昌市')
insert into tb values('013' , '012' , N'南昌县')
insert into tb values('014' , '012' , N'新建县')
insert into tb values('015' , '012' , N'进贤县')
insert into tb values('016' , '012' , N'安义县')
insert into tb values('017' , '013' , N'麻丘镇')
insert into tb values('018' , '011' , N'九江市')
go;with t as
(
    select px = cast(id as varchar(500)) , * from tb t
    where not exists(select 1 from tb where id = t.pid)
    union all
    select cast(b.px + ',' + a.id as varchar(500)) , a.*
    from tb a , t b where a.pid = b.id
)
select id , name , pid from t order by pxdrop table tb

--> 生成测试数据表: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,lvl=0     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)),lvl+1        FROM tb AS A        JOIN T AS B           ON A.pid=B.id)SELECT space(lvl*2)+Code Code,Name FROM T ORDER BY px/*
Code                           Name
------------------------------ ----------
01                             服装
  0101                         男装
    010101                     西装
      01010101                 全毛
      01010102                 化纤
    010102                     休闲装
  0102                         女装
    010201                     套装
    010202                     职业装
    010203                     休闲装
    010204                     西装
      01020401                 全毛
      01020402                 化纤
    010205                     休闲装(14 行受影响)
*/

解决方案 »