有个急待解决的，应该很简单的SQL查询问题！

mark2006 2009-10-04 09:52:53

表如下：
FID FName FParentID FFullID
100001 A 0 0.100001
100002 B 0 0.100002
100003 C 100001 0.100001.100003
100004 D 100003 0.100001.100003.100004

如何得到表

FID FName FParentID FFullID FFullName
100001 A 0 0.100001 A
100002 B 0 0.100002 B
100003 C 100001 0.100001.100003 A.C
100004 D 100003 0.100001.100003.100004 A.C.D

...全文

187 15 打赏收藏转发到动态举报

写回复

用AI写文章

15 条回复

切换为时间正序

请发表友善的回复…

发表回复

贝隆 2009-10-06

打赏
举报

学习

appleller 2009-10-06

打赏
举报

MARK

gsk09 2009-10-05

打赏
举报

[Quote=引用 9 楼 gsk09 的回复:]
引用 7 楼 simenyu 的回复:
引用 6 楼 feixianxxx 的回复:
SQL codecreatefunction kp1 (@FFullvarchar(22))returnsvarchar(39)asbegindeclare@svarchar(30)select@s=isnull(@s+'.','')+FNamefrom tkwherecharindex(RTRIM(FID),@FFull)>0return@sendcreateTABLE tk(FIDint,FN¡

如果改变100003和100004的先后顺序,那么函数不能返回正确的结果,如:
SQL codeFID FName FParentID FFullID FullName----------- ----- ----------- ---------------------- ---------------------------------------100001 A00.100001 A100002 B00.100002 B100004 D1000030.100001.100003.100004 A.D.C100003 C1000010.100001.100003 A.C

（所影响的行数为4 行）

create function kp1 (@FFull varchar(22))
returns varchar(39)
as
begin
declare @s varchar(30)
select @s=isnull(@s+'.','')+FName from tk where charindex(RTRIM(FID),@FFull)>0
　　　　order by charindex(RTRIM(FID),@FFull)
return @s
end
create TABLE tk(FID int,FName varchar(1),FParentID int,FFullID varchar(22))
INSERT INTO tk
SELECT 100001,'A',0,'0.100001' UNION ALL
SELECT 100002,'B',0,'0.100002' UNION ALL
SELECT 100003,'C',100001,'0.100001.100003' UNION ALL
SELECT 100004,'D',100003,'0.100001.100003.100004'

select *,
FullName =dbo.kp1(FFullID)
from tk

[/Quote]

错了

liangCK 2009-10-04

打赏
举报

-------------------------------------

--  Author : liangCK 梁爱兰

--  Comment: 小梁 爱 兰儿

--  Date   : 2009-10-04 12:34:14

-------------------------------------

 

--> 生成测试数据: @tb

DECLARE @tb TABLE (FID int,FName varchar(1),FParentID int,FFullID varchar(22))

INSERT INTO @tb

SELECT 100001,'A',0,'0.100001' UNION ALL

SELECT 100002,'B',0,'0.100002' UNION ALL

SELECT 100003,'C',100001,'0.100001.100003' UNION ALL

SELECT 100004,'D',100003,'0.100001.100003.100004'



--SQL查询如下:



SELECT A.*,STUFF(B.x,1,1,'') AS FullName

FROM @tb AS A OUTER APPLY 

 (SELECT x=(SELECT ','+FName AS [text()] FROM @tb

            WHERE CHARINDEX('.'+RTRIM(FID)+'.','.'+A.FFullID+'.') > 0

            FOR XML PATH(''),TYPE).value('.','varchar(100)')) AS B;



/*

FID         FName FParentID   FFullID                FullName

----------- ----- ----------- ---------------------- -----------------------

100001      A     0           0.100001               A

100002      B     0           0.100002               B

100003      C     100001      0.100001.100003        A,C

100004      D     100003      0.100001.100003.100004 A,C,D



(4 行受影响)





*/

黄_瓜 2009-10-04

打赏
举报

建议你把数据好好排序

mark2006 2009-10-04

打赏
举报

楼上的，不对啊
我要得到FFullName 而不是FFullID，因为我的FFullID已经有了啊

shuangfang 2009-10-04

打赏
举报

学习来了

dawugui 2009-10-04

打赏
举报



/*

标题：查询所有节点及其所有子节点的函数

作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 

时间：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

devilidea 2009-10-04

打赏
举报

学习下

feixianxxx 2009-10-04

打赏
举报

gsk09 2009-10-04

打赏
举报

[Quote=引用 7 楼 simenyu 的回复:]
引用 6 楼 feixianxxx 的回复:
SQL codecreatefunction kp1 (@FFullvarchar(22))returnsvarchar(39)asbegindeclare@svarchar(30)select@s=isnull(@s+'.','')+FNamefrom tkwherecharindex(RTRIM(FID),@FFull)>0return@sendcreateTABLE tk(FIDint,FN¡

如果改变100003和100004的先后顺序,那么函数不能返回正确的结果,如:
SQL codeFID FName FParentID FFullID FullName----------- ----- ----------- ---------------------- ---------------------------------------100001 A00.100001 A100002 B00.100002 B100004 D1000030.100001.100003.100004 A.D.C100003 C1000010.100001.100003 A.C

（所影响的行数为4 行）
[/Quote]

create function kp1 (@FFull varchar(22))
returns varchar(39)
as
begin
declare @s varchar(30)
select @s=isnull(@s+'.','')+FName from tk where charindex(RTRIM(FID),@FFull)>0
　　　　order by charindex(RTRIM(FID),@FFull)
return @s
end
create TABLE tk(FID int,FName varchar(1),FParentID int,FFullID varchar(22))
INSERT INTO tk
SELECT 100001,'A',0,'0.100001' UNION ALL
SELECT 100002,'B',0,'0.100002' UNION ALL
SELECT 100003,'C',100001,'0.100001.100003' UNION ALL
SELECT 100004,'D',100003,'0.100001.100003.100004'

select *,
FullName =dbo.kp1(FFullID)
from tk

luluandzhangzhang 2009-10-04

打赏
举报

呵呵有点对不上号

SIMENYU 2009-10-04

打赏
举报

[Quote=引用 6 楼 feixianxxx 的回复:]
SQL codecreatefunction kp1 (@FFullvarchar(22))returnsvarchar(39)asbegindeclare@svarchar(30)select@s=isnull(@s+'.','')+FNamefrom tkwherecharindex(RTRIM(FID),@FFull)>0return@sendcreateTABLE tk(FIDint,FN¡
[/Quote]

如果改变100003和100004的先后顺序,那么函数不能返回正确的结果,如:

FID         FName FParentID   FFullID                FullName                                

----------- ----- ----------- ---------------------- --------------------------------------- 

100001      A     0           0.100001               A

100002      B     0           0.100002               B

100004      D     100003      0.100001.100003.100004 A.D.C

100003      C     100001      0.100001.100003        A.C



（所影响的行数为 4 行）

feixianxxx 2009-10-04

打赏
举报

create  function kp1 (@FFull varchar(22))

returns varchar(39)

as

begin

declare @s varchar(30)

select @s=isnull(@s+'.','')+FName from tk where charindex(RTRIM(FID),@FFull)>0

return @s

end

create  TABLE tk(FID int,FName varchar(1),FParentID int,FFullID varchar(22))

INSERT INTO  tk

SELECT 100001,'A',0,'0.100001' UNION ALL

SELECT 100002,'B',0,'0.100002' UNION ALL

SELECT 100003,'C',100001,'0.100001.100003' UNION ALL

SELECT 100004,'D',100003,'0.100001.100003.100004'



select *,

FullName =dbo.kp1(FFullID)

from  tk



FID         FName FParentID   FFullID                FullName

----------- ----- ----------- ---------------------- ---------------------------------------

100001      A     0           0.100001               A

100002      B     0           0.100002               B

100003      C     100001      0.100001.100003        A.C

100004      D     100003      0.100001.100003.100004 A.C.D