如何建下图的表（多重嵌套），以及如何查询

[foxe]

数据存在多层嵌套，深度可能无限制，如何设计表来存储这种关系（其实是就是父子关系图），以及从顶级来查询出整个关于这级的子孙图。

[wzy_love_sly]

create table tb(id int,name varchar(50),parentid int)
insert into tb select 1,'安徽省',0
insert into tb select 2,'湖南省',0
insert into tb select 3,'合肥市',1
insert into tb select 4,'巢湖市',1
insert into tb select 5,'肥西县',3
insert into tb select 6,'肥东县',3
insert into tb select 7,'肥东乡',6
insert into tb select 8,'肥东村',7
insert into tb select 9,'湖南乡',2
insert into tb select 10,'湖南镇',9
go

with dom 
as
(select * from tb where parentid=3 
union all
select a.* from tb a inner join dom b on a.parentid=b.id)

select * from dom

[pl_mm]

if object_id('dom') is not null
drop table dom
go
create table dom(id int,name varchar(50),parentid int,Level int)
insert into dom select 1,'河北省',null,1
insert into dom select 2,'秦皇岛市',1,2
insert into dom select 3,'山海关区',2,3
insert into dom select 4,'海港区',2,3
insert into dom select 5,'北京',null,1
insert into dom select 6,'海淀',5,2
insert into dom select 7,'朝阳',5,2

go
if object_id('proc_delete_tb') is not null
drop proc proc_delete_tb
go
create proc proc_delete_tb
@id int
as
begin
delete dom where id = @id
while @@rowcount > 0
delete dom where parentid not in(select id from dom) and parentid is not null
end
go
exec proc_delete_tb 1 
select * from dom

这是删除的

[htl258_Tony]

应该雷同ERP软件的BOM多级展开吧,理论层次无限,但层次多了肯定影响查询速度.

[foxe]

就是这个意思，希望能高效简洁一些，谢谢大家帮助

[pl_mm]

最主要就是父编号parentid还有层数Level ，楼主还要简洁什么？

[foxe]

（wzy_love_sly）或各位，能否帮解释下查询语句

[foxe]

不是说以上设计不简洁，能否请帮指点下查询

[pl_mm]

[Quote=引用 7 楼 foxe 的回复:]
不是说以上设计不简洁，能否请帮指点下查询
[/Quote]
大哥，你哪不懂说啊，急死我了

[foxe]

查询，你的语句没问题，能看明白，是你楼上的

with dom 
as
(select * from tb where parentid=3 
union all
select a.* from tb a inner join dom b on a.parentid=b.id)

[肥龙上天]

以前好像遇见过，拔下坟

[pl_mm]

语法
 
[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
        expression_name [ ( column_name [ ,...n ] ) ]
    AS
        ( CTE_query_definition )

 

备注
创建和使用 CTE 的指南
下列指南应用于非递归 CTE。有关适用于递归 CTE 的指南，请参阅后面的“定义和使用递归 CTE 的指南”。

CTE 之后必须跟随引用部分或全部 CTE 列的 SELECT、INSERT、UPDATE 或 DELETE 语句。也可以在 CREATE VIEW 语句中将 CTE 指定为视图中 SELECT 定义语句的一部分。


可以在非递归 CTE 中定义多个 CTE 查询定义。定义必须与以下集合运算符之一结合使用：UNION ALL、UNION、INTERSECT 或 EXCEPT。


CTE 可以引用自身，也可以引用在同一 WITH 子句中预先定义的 CTE。不允许前向引用。


不允许在一个 CTE 中指定多个 WITH 子句。例如，如果 CTE_query_definition 包含一个子查询，则该子查询不能包括定义另一个 CTE 的嵌套的 WITH 子句。


不能在 CTE_query_definition 中使用以下子句：


COMPUTE 或 COMPUTE BY


ORDER BY（除非指定了 TOP 子句）


INTO 


带有查询提示的 OPTION 子句


FOR XML


FOR BROWSE


如果将 CTE 用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾。


可以使用引用 CTE 的查询来定义游标。


可以在 CTE 中引用远程服务器中的表。 


在执行 CTE 时，任何引用 CTE 的提示都可能与该 CTE 访问其基础表时发现的其他提示相冲突，这种冲突与引用查询中的视图的提示所发生的冲突相同。发生这种情况时，查询将返回错误。有关详细信息，请参阅视图解析。

[pl_mm]

至于怎么更好的使用，楼主可以查下sqlserver帮助，f1后索引输入cte
也可以去google一下sql2005cte

[pl_mm]

--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'

--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
	SET @Level=@Level+1
	INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
	FROM @t a,@t_Level b
	WHERE a.PID=b.ID
		AND b.Level=@Level-1
END

--显示结果
SELECT SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
  |--烟台市
    |--招远市
  |--青岛市
|--四会市
  |--清远市
    |--小分市
--*/

[pl_mm]

--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO

--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
	DECLARE @Level int
	SET @Level=1
	INSERT @t_Level SELECT @ID,@Level
	WHILE @@ROWCOUNT>0
	BEGIN
		SET @Level=@Level+1
		INSERT @t_Level SELECT a.ID,@Level
		FROM tb a,@t_Level b
		WHERE a.PID=b.ID
			AND b.Level=@Level-1
	END
	RETURN
END
GO

--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid('002') b
WHERE a.ID=b.ID
/*--结果
ID   PID  Name       
------ ------- ---------- 
002  001  烟台市
004  002  招远市
--*/

[肥龙上天]

create table #1(id int,pid int,sname nvarchar(20))
insert #1 values(1,0,'abc1')
insert #1 values(2,1,'abc2')
insert #1 values(3,1,'abc3')
insert #1 values(4,3,'abc4')
insert #1 values(5,3,'abc5')
insert #1 values(6,5,'abc6')
insert #1 values(7,6,'abc7')

create table #2 (id int,pid int,sname nvarchar(20),lev int)
declare @level int
set @level = 0

insert #2 select *,@level from #1 where id = 1
while @@rowcount > 0
begin
	set @level = @level + 1
	insert #2
	select a.*,@level
	from #1 a join #2 b on a.pid = b.id and b.lev = @level - 1
end

select * from #2 

id          pid         sname                lev
----------- ----------- -------------------- -----------
1           0           abc1                 0
2           1           abc2                 1
3           1           abc3                 1
4           3           abc4                 2
5           3           abc5                 2
6           5           abc6                 3
7           6           abc7                 4

(7 row(s) affected)

[foxe]

现需要为如下，每个父可能有子的不同组合而得到

create TABLE tb(ID char(3),SID char(3),Name nvarchar(10))

INSERT tb SELECT '1',NULL ,'1'
UNION ALL SELECT '2','1','A'
UNION ALL SELECT '3','2','a' 
UNION ALL SELECT '4','3','aa' 
UNION ALL SELECT '5','4','aaa' 
UNION ALL SELECT '6','5','aaaa' 
UNION ALL SELECT '7','1','B' 
UNION ALL SELECT '8','2','b'
UNION ALL SELECT '9','5','bbbb' 
UNION ALL SELECT '10','3','bb' 
UNION ALL SELECT '11','1','C' 
UNION ALL SELECT '12','10','eee' 
UNION ALL SELECT '13','4','bbb'
UNION ALL SELECT '14','10','ddd'
UNION ALL SELECT '15','5','gggg'

1
A
  |--1
a
  |--A
    |--1
aa
  |--a
    |--A
      |--1
aaa
  |--aa
    |--a
      |--A
        |--1