如何查询这样的语句
id,name,pid
1,a,
2,a1,1
3,a2,1
4,a3,1
5,b,
6,b1,5
7,b2,5
8,c,
9,d,
得到
a,a1,a2,a3
b,b1,b2
c,
d
[解决办法]
create table tb(id int,name varchar(10),pid int)
insert into tb values(1, 'a ',null)
insert into tb values(2, 'a1 ',1)
insert into tb values(3, 'a2 ',1)
insert into tb values(4, 'a3 ',1)
insert into tb values(5, 'b ',null)
insert into tb values(6, 'b1 ',5)
insert into tb values(7, 'b2 ',5)
insert into tb values(8, 'c ',null)
insert into tb values(9, 'd ',null)
go
select * into temp from
(
select id , name from tb where pid is null
union all
select pid id , name from tb where pid is not null
) t
order by id ,name
go
--创建一个合并的函数
create function f_hb(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ' '
select @str = @str + ', ' + cast(name as varchar) from temp where id = @id
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
--调用自定义函数得到结果：
select distinct id ,dbo.f_hb(id) as name from temp
drop table tb,temp
/*
id name
----------- ----------
1 a,a1,a2,a3
5 b,b1,b2
8 c
9 d
（所影响的行数为 4 行）
*/
[解决办法]
参考：
--生成测试数据
create table 表(部门 int,人员 varchar(20))
insert into 表 select 1, '张三 '
insert into 表 select 1, '李四 '
insert into 表 select 1, '王五 '
insert into 表 select 2, '赵六 '
insert into 表 select 2, '邓七 '
insert into 表 select 2, '刘八 '
go
--创建用户定义函数
create function f_str(@department int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ' '
select @ret = @ret+ ', '+人员 from 表 where 部门 = @department
set @ret = stuff(@ret,1,1, ' ')
return @ret
end
go
--执行
select 部门,人员=dbo.f_str(部门) from 表 group by 部门 order by 部门
go
--输出结果
/*
部门 人员
---- --------------
1 张三,李四,王五
2 赵六,邓七,刘八
*/
--删除测试数据
drop function f_str
drop table 表
go
[解决办法]
这个不需要临时表.
create table tb(id int,name varchar(10),pid int)
insert into tb values(1, 'a ',null)
insert into tb values(2, 'a1 ',1)
insert into tb values(3, 'a2 ',1)
insert into tb values(4, 'a3 ',1)
insert into tb values(5, 'b ',null)
insert into tb values(6, 'b1 ',5)
insert into tb values(7, 'b2 ',5)
insert into tb values(8, 'c ',null)
insert into tb values(9, 'd ',null)
go
select * from
(
select id , name from tb where pid is null
union all
select pid id , name from tb where pid is not null
) t
go
--创建一个合并的函数
create function f_hb(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ' '
select @str = @str + ', ' + cast(name as varchar) from
(
select id , name from tb where pid is null
union all
select pid id , name from tb where pid is not null
) t
where id = @id
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
--调用自定义函数得到结果：
select distinct id ,dbo.f_hb(id) as name from
(
select id , name from tb where pid is null
union all
select pid id , name from tb where pid is not null
) t
drop table tb
/*
id name
----------- ----------
1 a,a1,a2,a3
5 b,b1,b2
8 c
9 d
（所影响的行数为 4 行）
*/
[解决办法]
create function fn_test(@s varchar(1))
returns varchar(50)
as
begin
declare @str varchar(50)
set @str = ' '
select @str = @str+ ', '+name from tb1 where charindex(@s,name)> 0
set @str = stuff(@str,1,1, ' ')
return @str
end
go
update tb1 set pid = (case when isnull(pid, ' ') = ' ' then id else pid end)
select dbo.fn_test(name) from tb1 A where not exsits(select 1 from tb1 B where B.pid = A.pid And B.id <A.id)
[解决办法]
create table tb(id int,name varchar(10),pid int)
insert into tb values(1, 'a ',null)
insert into tb values(2, 'a1 ',1)
insert into tb values(3, 'a2 ',1)
insert into tb values(4, 'a3 ',1)
insert into tb values(5, 'b ',null)
insert into tb values(6, 'b1 ',5)
insert into tb values(7, 'b2 ',5)
insert into tb values(8, 'c ',null)
insert into tb values(9, 'd ',null)
create function dbo.aa(@a varchar(1))
returns varchar(100)
as
begin
declare @str varchar(100)
set @str= ' '
select @str=@str+ ', '+name from tb where left(name,1)=@a
return @str
end
select distinct dbo.aa(left(name,1)) from tb
----------------------------------------------------------------
,a,a1,a2,a3
,b,b1,b2
,c
,d
(4 row(s) affected)
[解决办法]
create table tb(id int,name varchar(10),pid int)
insert into tb values(1, 'a ',null)
insert into tb values(2, 'a1 ',1)
insert into tb values(3, 'a2 ',1)
insert into tb values(4, 'a3 ',1)
insert into tb values(5, 'b ',null)
insert into tb values(6, 'b1 ',5)
insert into tb values(7, 'b2 ',5)
insert into tb values(8, 'c ',null)
insert into tb values(9, 'd ',null)
go
select * into temp from
(
select id , name from tb where pid is null
union all
select pid id , name from tb where pid is not null
) t
go
declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' , max(case px when ' ' ' + cast(px as varchar) + ' ' ' then name else ' ' ' ' end) [name ' + cast(px as varchar) + '] '
from (select distinct px from (select px=(select count(1) from temp where id=a.id and name <a.name)+1 , * from temp a) t) as m
set @sql = @sql + ' from (select px=(select count(1) from temp where id=a.id and name <a.name)+1 , * from temp a) t group by id '
exec(@sql)
drop table tb,temp
/*
id name1 name2 name3 name4
----------- ---------- ---------- ---------- ----------
1 a a1 a2 a3
5 b b1 b2
8 c
9 d
*/
[解决办法]
楼上的只支持一层父子关系啊
[解决办法]
一手拿着本子，一手握着笔，静听高手的讨论。
