这种结构的字段如何查询出它的上级,(树结构的)

Question

查询数据得到这样的一个结构的数据,某张表有这样的字段
trrecode               name
10                     集团
10.01                  公司
10.01.02               部门
10.01.02.03            小组现在查询到一条记录
trrecode               name
10.01.02.03            小组
最后是通过这条数据查询出他的上级然后生成树结构
这里小组是末级.

Answer 1

--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据：test
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'test') 
AND type in (N'U')) 
DROP TABLE test
GO---->建表
create table test([trrecode] varchar(11),[name] varchar(4))
insert test
select '10','集团' union all
select '10.01','公司' union all
select '10.01.02','部门' union all
select '10.01.02.03','小组'
GO--> 查询结果--SELECT * FROM test
with t as
(
SELECT * FROM test where trrecode ='10.01.02.03'
union all
select s.* from test s join t on s.trrecode = left(t.trrecode,LEN(t.trrecode)-3)
where LEN(t.trrecode)-3 >0
)
select * from t
--> 删除表格
--DROP TABLE test

Answer 2

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

Answer 3

谢谢两位,
这里使用with以前基本很少用,只觉得是用户大量子查询才用到,
这里使用是不是可以理解为把需要递归的查询放入with里面?
就目前我的查询要求每隔点之间数据不是2位有的是3位
例如
01.02.003.0567
所以 s.trrecode = left(t.trrecode,LEN(t.trrecode)-3)
这里减去3就不合适了
不过我的表结构里有Gov,ParentGov                                当前节点ID     对应父节点ID
trrecode               name       Gov         ParentGov
01.02.003.0567         小组          4             3
01.02.003              部门          3             2我在琢磨下看下应该可以吧这个两个字段应用进去

Answer 4

恩恩问题解决了,把父节点弄进去
就不用
 s.trrecode = left(t.trrecode,LEN(t.trrecode)-3)
然后最后把查询结果在DISTINCT一下就出来,
接下来的就是通过父节点和子节点查询绑定树就完成

Answer 5

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'test') 
AND type in (N'U')) 
DROP TABLE test
GO---->建表
create table test([trrecode] varchar(11),[name] varchar(4))
insert test
select '10','集团' union all
select '10.01','公司' union all
select '10.01.02','部门' union all
select '10.01.02.03','小组'
GO
select * from test a 
where not exists
(select * from test b where b.trrecode like a.trrecode+'%' 
and a.trrecode<>b.trrecode)