表a
号码 父亲 母亲
A AF AM
AF AFF AFM
AM AMF AMM
AFF AFFF AFFM
AFM AFMF AFMM
......
如何高效的查询
问题1
如何查出给定代数A的系谱
号码 父 母 父父 父母 母父 母母 父母父 父母母
A AF AM AFF AFM AMF AMM AFMF AFMM问题2,如何穷举表中所有A的祖先
个体 第一代 第二代 第三代 ... 第n代
A AF AFF AFFF ... ...
AM AFM AFFM ... ...
AMF AFMF
AMM AFMM
AMFF
AMFM
AMMF
AMMM
号码 父亲 母亲
A AF AM
AF AFF AFM
AM AMF AMM
AFF AFFF AFFM
AFM AFMF AFMM
......
如何高效的查询
问题1
如何查出给定代数A的系谱
号码 父 母 父父 父母 母父 母母 父母父 父母母
A AF AM AFF AFM AMF AMM AFMF AFMM问题2,如何穷举表中所有A的祖先
个体 第一代 第二代 第三代 ... 第n代
A AF AFF AFFF ... ...
AM AFM AFFM ... ...
AMF AFMF
AMM AFMM
AMFF
AMFM
AMMF
AMMM
--测试数据
DECLARE @a TABLE(No varchar(10),Name varchar(10))
INSERT @a SELECT '101' ,'现金'
UNION ALL SELECT '102' ,'银行存款'
UNION ALL SELECT '10201','工行'
UNION ALL SELECT '10202','建行'
UNION ALL SELECT '10203','农行'DECLARE @b TABLE(No varchar(10),[Money] money )
INSERT @b SELECT '101' ,100
UNION ALL SELECT '10201',20
UNION ALL SELECT '10202',120--逐级汇总查询
SELECT a.No,a.Name,
[Money]=ISNULL(SUM([Money]),0)
FROM @a a
LEFT JOIN @b b ON b.No LIKE a.No+'%'
GROUP BY a.No,a.Name
ORDER BY a.No
/*--结果
No Name Money
---------------- ----------------- ---------------------
101 现金 100.0000
102 银行存款 140.0000
10201 工行 20.0000
10202 建行 120.0000
10203 农行 .0000
--*/
--测试数据
DECLARE @t TABLE(No varchar(10))
INSERT @t SELECT '1'
UNION ALL SELECT '3'
UNION ALL SELECT '302'
UNION ALL SELECT '305'
UNION ALL SELECT '305001'
UNION ALL SELECT '305005'
UNION ALL SELECT '6'
UNION ALL SELECT '601'--重排编码
UPDATE a SET
No=RIGHT(--重排第一层编码
(SELECT COUNT(DISTINCT No) FROM @t WHERE No<=a.No AND LEN(No)=1),1)
+CASE --重排第二层编码
WHEN LEN(No)>1
THEN RIGHT(100+
(SELECT COUNT(DISTINCT No) FROM @t WHERE No<=a.No AND No LIKE LEFT(a.NO,1)+'__'),2)
ELSE '' END
+CASE --重排第三层编码
WHEN LEN(No)>3
THEN RIGHT(1000+
(SELECT COUNT(DISTINCT No) FROM @t WHERE No<=a.No AND No LIKE LEFT(a.NO,3)+'___'),3)
ELSE '' END
FROM @t a--显示处理结果
SELECT * FROM @t
/*--结果
No
----------
1
2
201
202
202001
202002
3
301
--*/
--测试数据
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
/*--结果
|--山东省
|--烟台市
|--招远市
|--青岛市
|--四会市
|--清远市
|--小分市
--*/
--测试数据
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)
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END--显示结果
SELECT a.*
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Level,b.ID
/*--结果
ID PID Name
------- --------- ----------
001 NULL 山东省
005 NULL 四会市
002 001 烟台市
003 001 青岛市
006 005 清远市
004 002 招远市
007 006 小分市
--*/
--测试数据
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_Sort(@ID char(3)=NULL,@sort int=1)
RETURNS @t_Level TABLE(ID char(3),sort int)
AS
BEGIN
DECLARE tb CURSOR LOCAL
FOR
SELECT ID FROM tb
WHERE PID=@ID
OR(@ID IS NULL AND PID IS NULL)
OPEN TB
FETCH tb INTO @ID
WHILE @@FETCH_STATUS=0
BEGIN
INSERT @t_Level VALUES(@ID,@sort)
SET @sort=@sort+1
IF @@NESTLEVEL<32 --如果递归层数未超过32层(递归最大允许32层)
BEGIN
--递归查找当前节点的子节点
INSERT @t_Level SELECT * FROM f_Sort(@ID,@sort)
SET @sort=@sort+@@ROWCOUNT --排序号加上子节点个数
END
FETCH tb INTO @ID
END
RETURN
END
GO--显示结果
SELECT a.*
FROM tb a,f_Sort(DEFAULT,DEFAULT) b
WHERE a.ID=b.ID
ORDER BY b.sort
/*--结果
ID PID Name
------ --------- ----------
001 NULL 山东省
002 001 烟台市
004 002 招远市
003 001 青岛市
005 NULL 四会市
006 005 清远市
007 006 小分市
--*/
insert T select 'A', 'AF', 'AM'
insert T select 'AF', 'AFF', 'AFM'
insert T select 'AM', 'AMF', 'AMM'
insert T select 'AFF', 'AFFF', 'AFFM'
insert T select 'AFM', 'AFMF', 'AFMM'
go
create function F_tree(@号码 nvarchar(100))
returns @T table(ID int identity,个体 nvarchar(5),号码 nvarchar(5), 父母 nvarchar(5),flag bit,代 int)
as
begin
declare @i int
set @i=1
insert @T
select @号码,号码,父亲,0,@i from T where 号码=@号码
union
select @号码,号码,母亲,1,@i from T where 号码=@号码
while @@rowcount>0
begin
set @i=@i+1
insert @T
select
@号码,a.号码,a.父亲,0,@i
from
T a join @T b on a.号码=b.父母 and 代=@i-1
union
select
@号码,a.号码,a.母亲,1,@i
from
T a join @T b on a.号码=b.父母 and 代=@i-1
end
return
end
godeclare @s nvarchar(4000),@i int
select @i=max(代) from F_tree('A')
set @s=''
while @i>0
select @s=',[第'+rtrim(@i)+'代父亲]='+'max(case when 代='+rtrim(@i)+' and flag=0 then 父母 else ''''end)'+
',[第'+rtrim(@i)+'代母亲]='+'max(case when 代='+rtrim(@i)+' and flag=1 then 父母 else ''''end)'
+@s,@i=@i-1
exec('select 个体'+@s+'
from (select *,
con=(select count(1) from F_tree(''A'') where 代=a.代 and flag=a.flag and ID!>a.ID)
from F_tree(''A'') a )Tmp
group by 个体,con')---生成语句如下:select 个体,
[第1代父亲]=max(case when 代=1 and flag=0 then 父母 else ''end),
[第1代母亲]=max(case when 代=1 and flag=1 then 父母 else ''end),
[第2代父亲]=max(case when 代=2 and flag=0 then 父母 else ''end),
[第2代母亲]=max(case when 代=2 and flag=1 then 父母 else ''end),
[第3代父亲]=max(case when 代=3 and flag=0 then 父母 else ''end),
[第3代母亲]=max(case when 代=3 and flag=1 then 父母 else ''end)
from
(select *,
con=(select count(1) from F_tree('A') where 代=a.代 and flag=a.flag and ID!>a.ID)
from F_tree('A') a )Tmp
group by 个体,con
/*
个体 第1代父亲 第1代母亲 第2代父亲 第2代母亲 第3代父亲 第3代母亲
----- ----- ----- ----- ----- ----- -----
A AF AM AFF AFM AFFF AFFM
A AMF AMM AFMF AFMM(所影响的行数为 2 行)
*/
http://blog.csdn.net/roy_88/archive/2006/12/24/1458449.aspx