有两个表:
A(表)
A_id A_content(列)
(如)1 "A_content1"
2 "A_content2"
3 "A_content3"
. .B(表)
B_id A_id B_Content(列)
(如)1 1 "B_Content1"
2 1 "B_Content2"
3 1 "B_Content3"
4 2 "B_Content4"
5 2 "B_Content5"
6 3 "B_Content6"
7 3 "B_Content7"共有字段是A_id
我现在要得到
A_content R_Content(列)
A_content1 B_Content1+B_Content2+B_Content3
A_content2 B_Content4+B_Content5
A_content3 B_Content6+B_Content7这样用查询语句可以实现么?请高手给出SQL语句,感激涕零。
A(表)
A_id A_content(列)
(如)1 "A_content1"
2 "A_content2"
3 "A_content3"
. .B(表)
B_id A_id B_Content(列)
(如)1 1 "B_Content1"
2 1 "B_Content2"
3 1 "B_Content3"
4 2 "B_Content4"
5 2 "B_Content5"
6 3 "B_Content6"
7 3 "B_Content7"共有字段是A_id
我现在要得到
A_content R_Content(列)
A_content1 B_Content1+B_Content2+B_Content3
A_content2 B_Content4+B_Content5
A_content3 B_Content6+B_Content7这样用查询语句可以实现么?请高手给出SQL语句,感激涕零。
returns varchar(2000)
as
declare @t varchar(2000)
set @t = ''select @t = @t + '+' + B_Content from B where A_id = a_id
return right(@t,2,len(@t))
select A_content,T.B_Content
from A
left jon (select A_id,getB_Content(a_id) as B_Content from B group by A_id) T
on A.A_id = T.A_id
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + '+' + cast(B_Content as varchar) from B where A_id = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
gocreate table A(A_id int,A_content varchar(50))
insert A select 1,'A_content1'
union all select 2,'A_content2'
union all select 3,'A_content3'create table B(B_id int,A_id int,B_Content varchar(50))
insert B select 1,1 ,'B_Content1'
union all select 2,1 ,'B_Content2'
union all select 3,1 ,'B_Content3'
union all select 4,2 ,'B_Content4'
union all select 5,2 ,'B_Content5'
union all select 6,3 ,'B_Content6'
union all select 7,3 ,'B_Content7'
select A.A_Content,R_Content from A,(select A_ID,dbo.f_hb(A_ID) R_Content from B group by A_ID) B where A.A_id=B.A_iddrop table A,B
drop function f_hb
A_Content R_Content
-------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A_content1 B_Content1+B_Content2+B_Content3
A_content2 B_Content4+B_Content5
A_content3 B_Content6+B_Content7(所影响的行数为 3 行)
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str = ''select @str = @str + '+' + B_Content from B where A_id = a_id
return stuff(@str ,1,1,'')
end
select A_content,dbo.aaa(A_id) as B_Content from A
create table A(A_id int, A_content varchar(10))
insert A select 1, 'A_content1'
union all select 2, 'A_content2'
union all select 3, 'A_content3'
create table B(B_id int, A_id int, B_Content varchar(10))
insert B select 1, 1, 'B_Content1'
union all select 2, 1, 'B_Content2'
union all select 3, 1, 'B_Content3'
union all select 4, 2, 'B_Content4'
union all select 5, 2, 'B_Content5'
union all select 6, 3, 'B_Content6'
union all select 7, 3, 'B_Content7'create function fun(@A_id int)
returns varchar(1000)
as
begin
declare @re varchar(1000)
set @re=''
select @re=@re+B_Content+'+' from B where A_id=@A_id
select @re=left(@re, len(@re)-1) return @re
endselect distinct A.A_content, A_content=dbo.fun(A_id) from A
--result
A_content A_content
---------- ------------------------------------
A_content1 B_Content1+B_Content2+B_Content3
A_content2 B_Content4+B_Content5
A_content3 B_Content6+B_Content7(3 row(s) affected)
returns varchar(4000)
as
begin
declare @str varchar(4000)
set @str = ''select @str = @str + '+' + B_Content from B where A_id = a_id
return stuff(@str ,1,1,'')
end
go
select A_content,dbo.aaa(A_id) as B_Content from A
insert A select 1,'A_content1'
union all select 2,'A_content2'
union all select 3,'A_content3'create table B(B_id int,A_id int,B_Content varchar(50))
insert B select 1,1 ,'B_Content1'
union all select 2,1 ,'B_Content2'
union all select 3,1 ,'B_Content3'
union all select 4,2 ,'B_Content4'
union all select 5,2 ,'B_Content5'
union all select 6,3 ,'B_Content6'
union all select 7,3 ,'B_Content7'
--建函数
create function test_b(@a_id varchar(20))
returns varchar(1000)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+'+'+B_Content from b where a_id=@a_id
set @sql=stuff(@sql,1,1,'')
return (@sql)
endselect A_content,dbo.test_b(a_id)B_Content from a
A_content B_Content
-------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A_content1 B_Content1+B_Content2+B_Content3
A_content2 B_Content4+B_Content5
A_content3 B_Content6+B_Content7(所影响的行数为 3 行)