select id as 组,name as 组名字,count(id1) as 个数, id2 as 成员代码 from table1,table2 where table1.id1=table2.id1 group by id1
参考:create function getstr(@id Nchar(10)) returns Nvarchar(2000) as begin declare @str Nvarchar(2000) set @str=N'' select @str=@str+rtrim(bi2)+N',' from table2 where id1=@id return @str end GO调用: select id1,dbo.getstr(id2) as id2 from table2
create function getresult(@标识号 varchar(10)) returns varchar(100) as begin declare @result varchar(100) set @result='' select @result=@result+id2 from a2 where id1=@标识号 return @result end select a.id1 as 组,a.name as 组名字,count(b.id1) as 个数 , dbo.getresult(b.id1) 成员代码 from a1 a join a2 b on a.id1=b.id1 group by a.id1,a.name ,dbo.getresult(b.id1)----组id ,组名字, 个数, 成员代码 0 a 2 1 2 1 b 2 3 4 2 c 2 1 3
create table a(id1 int,name varchar(20)) insert into a values(0, 'a') insert into a values(1, 'b') insert into a values(2, 'c') go create table b(id1 int,id2 int) insert into b values(0 , 1) insert into b values(0 , 2) insert into b values(1 , 3) insert into b values(1 , 4) insert into b values(2 , 1) insert into b values(2 , 3) select * from a select * from b go create function get(@id int) returns varchar(8000) begin declare @x varchar(8000) select @x='' select @x=@x+cast(id2 as varchar(20))+',' from b where id1=@id return @xend go则选择id1=0的纪录为: select a.id1 组id,name 组名字,count(b.id1) 个数,left(dbo.get(0),len(dbo.get(0))-1) 成员代码 from a ,b where a.id1=0 and a.id1=b.id1 group by a.id1,a.name
create table a(id1 int,name varchar(20)) insert into a values(0, 'a') insert into a values(1, 'b') insert into a values(2, 'c') go create table b(id1 int,id2 int) insert into b values(0 , 1) insert into b values(0 , 2) insert into b values(1 , 3) insert into b values(1 , 4) insert into b values(2 , 1) insert into b values(2 , 3) select * from a select * from b go create function get(@id int) returns varchar(8000) begin declare @x varchar(8000) select @x='' select @x=@x+cast(id2 as varchar(20))+',' from b where id1=@id return @xend go--则选择id1=0的纪录为: select a.id1 组id,name 组名字,count(b.id1) 个数,left(dbo.get(b.id1),len(dbo.get(b.id1))-1) 成员代码 from a ,b where a.id1=b.id1 group by a.id1,a.name,left(dbo.get(b.id1),len(dbo.get(b.id1))-1)drop table a drop table b drop function get 表a: id1 name ----------- -------------------- 0 a 1 b 2 c表b id1 id2 ----------- ----------- 0 1 0 2 1 3 1 4 2 1 2 3 查询结果为: 组id 组名字 个数 成员代码 ----------- ----------- ----------- 0 a 2 1,2 1 b 2 3,4 2 c 2 1,3
非常感谢大家探讨,希望能进FAQ里
use NorthWind goif exists(select name from sysobjects where name='table1' and xtype='U') drop table table1 if exists(select name from sysobjects where name='table2' and xtype='U') drop table table2create table table1(id1 varchar(20), [name] varchar(10)) create table table2(id1 varchar(20), id2 varchar(10))insert into table1 values('0','a') insert into table1 values('1','b') insert into table1 values('2','c')insert into table2 values('0','1') insert into table2 values('0','2') insert into table2 values('1','3') insert into table2 values('1','4') insert into table2 values('2','1') insert into table2 values('2','3') goif exists(select name from sysobjects where name='getstr' and xtype='FN') drop function getstr gocreate function dbo.getstr(@id Nchar(10)) returns Nvarchar(2000) as begin declare @str Nvarchar(2000) set @str=N'' select @str=@str+rtrim(id2)+N',' from table2 where id1=@id return left(@str,len(@str)-1) end goSELECT A.id1, A.[name], (SELECT COUNT(*) FROM table2 B WHERE B.id1=A.id1) AS NUM, dbo.getstr(A.id1) FROM table1 A WHERE A.id1='0'
returns Nvarchar(2000)
as
begin
declare @str Nvarchar(2000)
set @str=N''
select @str=@str+rtrim(bi2)+N',' from table2
where id1=@id
return @str
end
GO调用:
select id1,dbo.getstr(id2) as id2 from table2
returns varchar(100)
as
begin
declare @result varchar(100)
set @result=''
select @result=@result+id2 from a2 where id1=@标识号
return @result
end
select a.id1 as 组,a.name as 组名字,count(b.id1) as 个数 , dbo.getresult(b.id1) 成员代码
from a1 a join a2 b on a.id1=b.id1
group by a.id1,a.name ,dbo.getresult(b.id1)----组id ,组名字, 个数, 成员代码
0 a 2 1 2
1 b 2 3 4
2 c 2 1 3
insert into a values(0, 'a')
insert into a values(1, 'b')
insert into a values(2, 'c')
go
create table b(id1 int,id2 int)
insert into b values(0 , 1)
insert into b values(0 , 2)
insert into b values(1 , 3)
insert into b values(1 , 4)
insert into b values(2 , 1)
insert into b values(2 , 3)
select * from a
select * from b
go
create function get(@id int)
returns varchar(8000)
begin
declare @x varchar(8000)
select @x=''
select @x=@x+cast(id2 as varchar(20))+',' from b where id1=@id
return @xend
go则选择id1=0的纪录为:
select a.id1 组id,name 组名字,count(b.id1) 个数,left(dbo.get(0),len(dbo.get(0))-1) 成员代码
from a ,b
where a.id1=0 and a.id1=b.id1
group by a.id1,a.name
insert into a values(0, 'a')
insert into a values(1, 'b')
insert into a values(2, 'c')
go
create table b(id1 int,id2 int)
insert into b values(0 , 1)
insert into b values(0 , 2)
insert into b values(1 , 3)
insert into b values(1 , 4)
insert into b values(2 , 1)
insert into b values(2 , 3)
select * from a
select * from b
go
create function get(@id int)
returns varchar(8000)
begin
declare @x varchar(8000)
select @x=''
select @x=@x+cast(id2 as varchar(20))+',' from b where id1=@id
return @xend
go--则选择id1=0的纪录为:
select a.id1 组id,name 组名字,count(b.id1) 个数,left(dbo.get(b.id1),len(dbo.get(b.id1))-1) 成员代码
from a ,b
where a.id1=b.id1
group by a.id1,a.name,left(dbo.get(b.id1),len(dbo.get(b.id1))-1)drop table a
drop table b
drop function get
表a:
id1 name
----------- --------------------
0 a
1 b
2 c表b
id1 id2
----------- -----------
0 1
0 2
1 3
1 4
2 1
2 3
查询结果为:
组id 组名字 个数 成员代码
----------- ----------- -----------
0 a 2 1,2
1 b 2 3,4
2 c 2 1,3
goif exists(select name from sysobjects where name='table1' and xtype='U')
drop table table1
if exists(select name from sysobjects where name='table2' and xtype='U')
drop table table2create table table1(id1 varchar(20), [name] varchar(10))
create table table2(id1 varchar(20), id2 varchar(10))insert into table1 values('0','a')
insert into table1 values('1','b')
insert into table1 values('2','c')insert into table2 values('0','1')
insert into table2 values('0','2')
insert into table2 values('1','3')
insert into table2 values('1','4')
insert into table2 values('2','1')
insert into table2 values('2','3')
goif exists(select name from sysobjects where name='getstr' and xtype='FN')
drop function getstr
gocreate function dbo.getstr(@id Nchar(10))
returns Nvarchar(2000)
as
begin
declare @str Nvarchar(2000)
set @str=N''
select @str=@str+rtrim(id2)+N',' from table2
where id1=@id
return left(@str,len(@str)-1)
end
goSELECT A.id1, A.[name], (SELECT COUNT(*) FROM table2 B WHERE B.id1=A.id1) AS NUM, dbo.getstr(A.id1)
FROM table1 A WHERE A.id1='0'