# 统计表有关问题，怎么把相关的id放到某字段里

www.myexceptions.net  网友分享于：2013-03-01  浏览：5次

insert   into   table2   (a,b,c)
select   count(*),A,B   from   table   group   by   A,B,C,D

------解决方案--------------------
create function fn_test(@A varchar(10),@B varchar(10),@C varchar(10),@D varchar(10))
retuens varchar(50)
as
begin
declare @str varchar(50)
set @str = ' '
select @str = @str + ', ' + id from table where A = @A and B= @B and C = @C and D = @D
set @str = stuff(@str,1,1, ' ')
return @str
end
go
insert table2 (Id,a,b,c)
select Id=dbo.fn_test(A,B,C,D),count(1),A,B group by A,B,C,D
------解决方案--------------------
drop table A
go
create table A(id int,A char(1),B char(1),C char(1))
insert into A
select 1, 'a ', 'b ', 'c '
union all select 2, 'a ', 'b ', 'c '
union all select 3, 'b ', 'c ', 'd '
union all select 4, 'a ', 'b ', 'c '
union all select 5, 'a ', 'b ', 'c '
go
create function dbo.uf_gets(@A char(1),@B char(1),@C char(1))
returns varchar(100)
as
begin
declare @s varchar(100)
set @s= ' '
select @s=@s+ ', '+rtrim(id) from A where A=@A and B=@B and C=@C
set @s=stuff(@s,1,1, ' ')
return @s
end
GO
insert into B
select count(*) as sum,dbo.uf_gets(A,B,C) from A group by A,B,C
------解决方案--------------------
----创建测试数据
if object_id( 'tbTest ') is not null
drop table tbTest
if object_id( 'fnTest ') is not null
drop function fnTest
GO
create table tbTest(id int, A varchar(5), B varchar(5), C varchar(6))
insert tbTest
select 1, 'a ', 'b ', 'c ' union all
select 2, 'a ', 'b ', 'c ' union all
select 3, 'b ', 'c ', 'd ' union all
select 4, 'a ', 'b ', 'c ' union all
select 5, 'a ', 'b ', 'c '
GO
create function fnTest(@A varchar(5),@B varchar(5),@C varchar(5))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ' '
select @str = @str + ', ' + rtrim(id) from tbTest
where A = @A and B = @B and C= @C
return stuff(@str,1,1, ' ')
end
GO

----查询
SELECT count(*) as num,dbo.fnTest(A,B,C) as idlist from tbTest group by A,B,C

----清除测试环境
drop table tbTest
drop function fnTest

/*结果
num idlist
----------- ---------
4 1,2,4,5
1 3
*/