34,838
社区成员
发帖
与我相关
我的任务
分享烦请各位高位实现此SQL语句
ID ParentID Location
1 NULL I:
2 1 Filescenter
3 2 品质部
4 3 Archives
5 4 Desktop.ini
6 3 显示桌面.scf
7 3 Thumbs.db
8 3 Thumbs.db:encryptable
9 3 测 试 计 划 表.doc
10 3 杂文
11 10 Internal Audit.xls
12 3 生产品质
13 12 #1009产品每星期不良率.xls
14 12 633问题图片.xls
我要实现的结果集为
I:/filescenter/品质部/archives/desktop.ini
I:/filescenter/品质部/显示桌面.scf
I:/filescenter/品质部/Thumbs.db
I:/filescenter/品质部/杂文/Internal Audit.xls
I:/filescenter/品质部/生产品质/#1009产品每星期不良率.xls
能否用一条SQL语句实现，或用堆栈实现?
1 NULL I:
2 1 Filescenter
3 2 品质部
4 3 Archives
5 4 Desktop.ini
6 3 显示桌面.scf
7 3 Thumbs.db
8 3 Thumbs.db:encryptable
9 3 测 试 计 划 表.doc
10 3 杂文
11 10 Internal Audit.xls
12 3 生产品质
13 12 #1009产品每星期不良率.xls
14 12 633问题图片.xls
我要实现的结果集为
I:/filescenter/品质部/archives/desktop.ini
I:/filescenter/品质部/显示桌面.scf
I:/filescenter/品质部/Thumbs.db
I:/filescenter/品质部/杂文/Internal Audit.xls
I:/filescenter/品质部/生产品质/#1009产品每星期不良率.xls
能否用一条SQL语句实现，或用堆栈实现?
...全文
请发表友善的回复…
发表回复
-狙击手- 2009-12-31
- 打赏
- 举报
-- Test Data: ta
IF OBJECT_ID('[ta]') IS NOT NULL
DROP TABLE [ta]
Go
CREATE TABLE ta([ID] INT,[ParentID] NVARCHAR(4),[Location] NVARCHAR(21))
Go
INSERT INTO ta
SELECT 1,Null,'I:' UNION ALL
SELECT 2,'1','Filescenter' UNION ALL
SELECT 3,'2','品质部' UNION ALL
SELECT 4,'3','Archives' UNION ALL
SELECT 5,'4','Desktop.ini' UNION ALL
SELECT 6,'3','显示桌面.scf' UNION ALL
SELECT 7,'3','Thumbs.db' UNION ALL
SELECT 8,'3','Thumbs.db:encryptable' UNION ALL
SELECT 9,'3','测 试 计 划 表.doc' UNION ALL
SELECT 10,'3','杂文' UNION ALL
SELECT 11,'10','Internal Audit.xls' UNION ALL
SELECT 12,'3','生产品质' UNION ALL
SELECT 13,'12','#1009产品每星期不良率.xls' UNION ALL
SELECT 14,'12','633问题图片.xls'
GO
--Start
--创建用户定义函数,每个子节点de父节点的信息
create function f_getParent1(@ID int)
returns varchar(40)
as
begin
declare @ret varchar(4000),@bid int
-- set @Id = 14
if not exists(select 1 from ta where parentid = @Id)
set @ret = '->all'
while exists(select 1 from ta where ID=@ID and parentID is not Null)
begin
select @ID=b.ID,@bid = a.parentid,@ret='\'+rtrim(b.[Location])+isnull(@ret,'')
from
ta a,ta b
where
a.ID=@ID and b.ID=a.parentID
-- print @ret
end
set @ret=stuff(@ret,1,1,'')
return @ret
end
go
--执行查询
select ID,replace(isnull(dbo.f_getParent1(ID),''),'->all','')+'\'+[Location] as parentID from ta
where right(isnull(dbo.f_getParent1(ID),''),5) = '->all'
go
drop function f_getParent1
--Result:
/*
ID parentID
----------- -----------------------------------------
5 I:\Filescenter\品质部\Archives\Desktop.ini
6 I:\Filescenter\品质部\显示桌面.scf
7 I:\Filescenter\品质部\Thumbs.db
8 I:\Filescenter\品质部\Thumbs.db:encryptable
9 I:\Filescenter\品质部\测 试 计 划 表.doc
11 I:\Filescenter\品质部\杂文\Internal Audit.xls
13 I:\Filescenter\品质部\生产品质\#1009产品每星期不良率.xls
14 I:\Filescenter\品质部\生产品质\633问题图片.xls
(8 行受影响)
*/
--End 
hjb111 2009-12-31
- 打赏
- 举报
楼上高人的问题 可做参考，但还没想到如何实现我所要求达到的结果集了，拜托一下，想想如何实现?谢了
--小F-- 2009-12-31
- 打赏
- 举报
*
标题：查询各节点的父路径函数
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-05-12
地点：广东深圳
*/
/*
原始数据及要求结果如下：
--食品
--水果
--香蕉
--苹果
--蔬菜
--青菜
id pid name
----------- ----------- --------------------
1 0 食品
2 1 水果
3 1 蔬菜
4 2 香蕉
5 2 苹果
6 3 青菜
要求得到各节点的父路径即如下结果：
id pid name 路径
--- --- ----- ---------------
1 0 食品 食品
2 1 水果 食品,水果
3 1 蔬菜 食品,蔬菜
4 2 香蕉 食品,水果,香蕉
5 2 苹果 食品,水果,苹果
6 3 青菜 食品,蔬菜,青菜
*/
create table tb (id int , pid int , name nvarchar(20))
insert into tb values(1 , 0 , '食品')
insert into tb values(2 , 1 , '水果')
insert into tb values(3 , 1 , '蔬菜')
insert into tb values(4 , 2 , '香蕉')
insert into tb values(5 , 2 , '苹果')
insert into tb values(6 , 3 , '青菜')
go
--查询各节点的父路径函数
create function f_pid(@id int) returns varchar(100)
as
begin
declare @re_str as varchar(100)
set @re_str = ''
select @re_str = name from tb where id = @id
while exists (select 1 from tb where id = @id and pid <> 0)
begin
select @id = b.id , @re_str = b.name + ',' + @re_str from tb a , tb b where a.id = @id and a.pid = b.id
end
return @re_str
end
go
select * , dbo.f_pid(id) 路径 from tb order by id
drop table tb
drop function f_pid
SQL code
/*
标题：查询所有节点及其所有子节点的函数
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)
时间：2009-04-12
地点：广东深圳
*/
--生成测试数据
create table tb(id varchar(10),pid varchar(10))
insert into tb select 'a', null
insert into tb select 'b', 'a'
insert into tb select 'c', 'a'
insert into tb select 'd', 'b'
insert into tb select 'e', 'b'
insert into tb select 'f', 'c'
insert into tb select 'g', 'c'
go
--创建用户定义函数
create function f_getchild(@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_getchild(id) , '') from tb group by id
go
--输出结果
/*
id children
---------- -------------
a a,b,c,d,e,f,g
b b,d,e
c c,f,g
d d
e e
f f
g g
（所影响的行数为 7 行）
*/
--删除测试数据
drop function f_getchild
drop table tb
SQL code
/*
标题：查询所有顶级节点及其子节点的例
地址：http://topic.csdn.net/u/20090323/21/63a91f51-c4df-464d-ba18-64343deb4e3a.html
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)
时间：2009-03-23
地点：广东深圳
*/
[code=SQL]create table Area (id int identity,Name varchar(10) ,order_by int ,father_ID int )
insert into area values('广东省',2,0)
insert into area values('四川省',2,0)
insert into area values('湖北省',2,0)
insert into area values('东莞市',1,1)
insert into area values('广州市',1,1)
insert into area values('天河区',0,5)
insert into area values('绵阳市',1,2)
insert into area values('武汉市',1,3)
insert into area values('汉口区',0,8)
insert into area values('随州市',1,3)
go
select * from area
drop table area
/*
id Name order_by father_ID
----------- ---------- ----------- -----------
1 广东省 2 0
2 四川省 2 0
3 湖北省 2 0
4 东莞市 1 1
5 广州市 1 1
6 天河区 0 5
7 绵阳市 1 2
8 武汉市 1 3
9 汉口区 0 8
10 随州市 1 3
（所影响的行数为 10 行）
要求显示为：
name
--------------
广东省
东莞市
广州市
天河区
四川省
绵阳市
湖北省
武汉市
汉口区
随州市
（所影响的行数为 10 行）
*/
SQL code
--创建原始表
create table Area (id int identity,Name varchar(10) ,order_by int ,father_ID int )
insert into area values('广东省',2,0)
insert into area values('四川省',2,0)
insert into area values('湖北省',2,0)
insert into area values('东莞市',1,1)
insert into area values('广州市',1,1)
insert into area values('天河区',0,5)
insert into area values('绵阳市',1,2)
insert into area values('武汉市',1,3)
insert into area values('汉口区',0,8)
insert into area values('随州市',1,3)
--创建临时表
create table tmp (id int identity,Name varchar(10) ,order_by int ,father_ID int )
go
--创建查询指定节点及其所有子节点的函数
create function f_cid(@ID int) returns @t_level table(id int , level int)
as
begin
declare @level int
set @level = 1
insert into @t_level select @id , @level
while @@ROWCOUNT > 0
begin
set @level = @level + 1
insert into @t_level select a.id , @level
from area a , @t_Level b
where a.father_ID = b.id and b.level = @level - 1
end
return
end
go
--创建存储过程并将数据插入临时表
create proc my_proc
as
begin
declare @id as int
set @id = 0
while exists(select 1 from area where order_by = 2 and id > @id)
begin
set @id = (select min(id) from area where order_by = 2 and id > @id)
insert into tmp(Name ,order_by ,father_ID) select a.name,a.order_by ,a.father_id from area a , f_cid(@id) b where a.id = b.id order by a.id
end
end
go
exec my_proc
--从临时表提取数据并显示
select case when order_by = 2 then name
when order_by = 1 then ' ' + name
when order_by = 0 then ' ' + name
end name
from tmp order by id
drop function f_cid
drop proc my_proc
drop table area , tmp
/*
name
--------------
广东省
东莞市
广州市
天河区
四川省
绵阳市
湖北省
武汉市
汉口区
随州市
（所影响的行数为 10 行）
hjb111 2009-12-31
- 打赏
- 举报
回楼上，跟BOM性质一样，只是不要没有文件名的数据集！
-狙击手- 2009-12-31
- 打赏
- 举报
bom
xman_78tom 2009-12-31
- 打赏
- 举报
CREATE TABLE tab([ID] INT,[ParentID] INT,[Location] NVARCHAR(21))
Go
INSERT INTO tab
SELECT 1,Null,N'I:' UNION ALL
SELECT 2,1,N'Filescenter' UNION ALL
SELECT 3,2,N'品质部' UNION ALL
SELECT 4,3,N'Archives' UNION ALL
SELECT 5,4,N'Desktop.ini' UNION ALL
SELECT 6,3,N'显示桌面.scf' UNION ALL
SELECT 7,3,N'Thumbs.db' UNION ALL
SELECT 8,3,N'Thumbs.db:encryptable' UNION ALL
SELECT 9,3,N'测 试 计 划 表.doc' UNION ALL
SELECT 10,3,N'杂文' UNION ALL
SELECT 11,10,N'Internal Audit.xls' UNION ALL
SELECT 12,3,N'生产品质' UNION ALL
SELECT 13,12,N'#1009产品每星期不良率.xls' UNION ALL
SELECT 14,12,N'633问题图片.xls'
GO
--drop table tab
create function ufn_bom(@id int)
returns nvarchar(4000) as
begin
declare @loc nvarchar(4000)
;with cte as(
select ParentID,Location from tab where ID=@id
union all
select t1.ParentID,t1.Location from tab t1 join cte t2 on t2.ParentID=t1.ID
)
select @loc=stuff((select N'\'+t2.Location from tab t1
join cte t2 on t2.ParentID=t1.ID for xml path(N'')),1,1,
(select Location+N'\' from tab where ParentID is null))
return @loc
end
select dbo.ufn_bom(ID) from tab where ID NOT IN
(select distinct ParentID from tab where ParentID is not null)
/*
I:\Filescenter\品质部\Archives\Desktop.ini
I:\Filescenter\品质部\显示桌面.scf
I:\Filescenter\品质部\Thumbs.db
I:\Filescenter\品质部\Thumbs.db:encryptable
I:\Filescenter\品质部\测 试 计 划 表.doc
I:\Filescenter\品质部\杂文\Internal Audit.xls
I:\Filescenter\品质部\生产品质\#1009产品每星期不良率.xls
I:\Filescenter\品质部\生产品质\633问题图片.xls
*/
