需求:
@t1表中存储了父子关系,根据@t2中指定的ID,回溯其父子关系的数据
---------------------------------------------------------------
DECLARE @t1 TABLE (ParentId INT,ChildId INT)
INSERT INTO @t1
SELECT 100,105 UNION
SELECT 103,105 UNION
SELECT 100,103 UNION
SELECT 101,102 UNION
SELECT 101,104
SELECT * FROM @t1 ORDER BY ChildId
---------------------------------------------------------
DECLARE @t2 TABLE (currentId INT)
INSERT INTO @t2
SELECT 102 UNION
SELECT 104 UNION
SELECT 105SELECT * FROM @t2
------------------------------------------------------------
/* 期望结果:
当前ID 所在位置
102 101->102
104 101
105 100->103->105
*/
@t1表中存储了父子关系,根据@t2中指定的ID,回溯其父子关系的数据
---------------------------------------------------------------
DECLARE @t1 TABLE (ParentId INT,ChildId INT)
INSERT INTO @t1
SELECT 100,105 UNION
SELECT 103,105 UNION
SELECT 100,103 UNION
SELECT 101,102 UNION
SELECT 101,104
SELECT * FROM @t1 ORDER BY ChildId
---------------------------------------------------------
DECLARE @t2 TABLE (currentId INT)
INSERT INTO @t2
SELECT 102 UNION
SELECT 104 UNION
SELECT 105SELECT * FROM @t2
------------------------------------------------------------
/* 期望结果:
当前ID 所在位置
102 101->102
104 101
105 100->103->105
*/
解决方案 »
- 禁用 windows身份验证
- 请教“制单日期”与“会计期”的设计问题
- 数据为何删不掉?
- 请问怎么同步两台不能互联的SQL SERVER 2005?
- 將表裡面的數據導成腳本
- 求sql:得到所占份额及累计份额
- 高分求sqlserver写excel
- 再sql存储过程中创建了一个临时表,请问如何使用这张临时表生成一张报表。请高手赐教,奉上100分。。
- 紧急急救!!!谁知道 Oracle JDeveloper 版本 3.1 从哪儿下载?
- adodb.recordset.opn执行后,符合判断是否取得数据集,或为数量为空
- 两个表的操作求SQL语句或者解决办法
- 新手求助!Eclipse中连接sqlserver2000数据库,用maven管理包,出现如下错误
--生成测试数据
create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null , '广东省')
insert into tb values('002' , '001' , '广州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河区')
insert into tb values('005' , '003' , '罗湖区')
insert into tb values('006' , '003' , '福田区')
insert into tb values('007' , '003' , '宝安区')
insert into tb values('008' , '007' , '西乡镇')
insert into tb values('009' , '007' , '龙华镇')
insert into tb values('010' , '007' , '松岗镇')
go--创建用户定义函数
create function f_cid(@id varchar(10)) returns varchar(8000)
as
begin
declare @i int , @ret varchar(8000)
declare @t table(id varchar(10) , pid varchar(10) , level int)
set @i = 1
insert into @t select id , pid , @i from tb where id = @id
while @@rowcount <> 0
begin
set @i = @i + 1
insert into @t select a.id , a.pid , @i from tb a , @t b where a.pid = b.id and b.level = @i - 1
end
select @ret = isnull(@ret , '') + id + ',' from @t
return left(@ret , len(@ret) - 1)
end
go --执行查询
select id , children = isnull(dbo.f_cid(id) , '') from tb group by iddrop table tb
drop function f_cid/*
id children
---- ---------------------------------------
001 001,002,003,004,005,006,007,008,009,010
002 002,004
003 003,005,006,007,008,009,010
004 004
005 005
006 006
007 007,008,009,010
008 008
009 009
010 010*/(所影响的行数为 10 行)
create table tb(id varchar(3) , pid varchar(3) , name nvarchar(10))
insert into tb values('001' , null , 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'松岗镇')
go;with t as
(
select id , cid = id from tb
union all
select t.id , cid = tb.id
from t join tb on tb.pid = t.cid
)
select id , cid = STUFF((SELECT ',' + rtrim(cid) FROM t WHERE id = tb.id FOR XML PATH('')) , 1 , 1 , '')
from tb
group by id
order by id
/*
id cid
---- ---------------------------------------
001 001,002,003,005,006,007,008,009,010,004
002 002,004
003 003,005,006,007,008,009,010
004 004
005 005
006 006
007 007,008,009,010
008 008
009 009
010 010(10 行受影响)
*/;with t as
(
select id , name , cid = id , path = cast(name as nvarchar(100)) from tb
union all
select t.id , t.name , cid = tb.id , path = cast(tb.name as nvarchar(100))
from t join tb on tb.pid = t.cid
)
select id , name ,
cid = STUFF((SELECT ',' + rtrim(cid) FROM t WHERE id = tb.id FOR XML PATH('')) , 1 , 1 , ''),
path = STUFF((SELECT ',' + path FROM t WHERE id = tb.id FOR XML PATH('')) , 1 , 1 , '')
from tb
group by id , name
order by id
/*
id name cid path
---- ---------- ------------------------------------------- ---------------------------------------------------------------------
001 广东省 001,002,003,005,006,007,008,009,010,004 广东省,广州市,深圳市,罗湖区,福田区,宝安区,西乡镇,龙华镇,松岗镇,天河区
002 广州市 002,004 广州市,天河区
003 深圳市 003,005,006,007,008,009,010 深圳市,罗湖区,福田区,宝安区,西乡镇,龙华镇,松岗镇
004 天河区 004 天河区
005 罗湖区 005 罗湖区
006 福田区 006 福田区
007 宝安区 007,008,009,010 宝安区,西乡镇,龙华镇,松岗镇
008 西乡镇 008 西乡镇
009 龙华镇 009 龙华镇
010 松岗镇 010 松岗镇(10 行受影响)
*/drop table tb
declare @t1 table (parentid int,childid int)
insert into @t1
select 103,105 union
select 100,103 union
select 101,102 union
select 101,104---------------------------------------------------------
declare @t2 table (currentid int)
insert into @t2
select 102 union
select 104 union
select 105
;with ach as
(
select childid,parentid as rid,parentid from @t1
union all
select a.childid,b.parentid as rid,b.parentid
from ach a join @t1 b on a.parentid = b.childid
)select currentid,
ltrim(currentid)+'->'
+replace(replace(stuff((select N'=+'+ltrim(rid) from ach where childid = t.currentid for xml path('')),1,2,''),'=','-'),'+','>') location
from @t2 t
group by currentid/************************currentid location
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
102 102->101
104 104->101
105 105->103->100(3 行受影响)
INSERT INTO @t1
SELECT 100,105 UNION
SELECT 103,105 UNION
SELECT 100,103 UNION
SELECT 101,102 UNION
SELECT 101,104---------------------------------------------------------
DECLARE @t2 TABLE (currentId INT)
INSERT INTO @t2
SELECT 102 UNION
SELECT 104 UNION
SELECT 105
;with cte as
(
select ParentId, ChildId,cast(LTRIM(ParentId)+'->'+LTRIM(ChildId) as varchar) as placement from @t1 a where Not exists(select 1 from @t1 b where a.ParentId=b.ChildId)
union all
select b.ParentId,b.ChildId,cast(a.placement+'->'+ltrim(b.ChildId) as varchar) as placement from cte a join @t1 b on a.ChildId=b.ParentId
)
select b.currentId,a.placement from cte a join @t2 b on a.ChildId=b.currentId/*
currentId placement
105 100->105
102 101->102
104 101->104
105 100->103->105
*/
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