我有两个表,A表和B表,A表的记录 B表的记录A1 A1 AAA
A2 A1 BBB
A3 A3 ABC
A3 CBA
要写一条怎样的Sql语句才能得出一下结果呢???
A1 AAA,BBB
A2 null
A3 ABC,CBA
A2 A1 BBB
A3 A3 ABC
A3 CBA
要写一条怎样的Sql语句才能得出一下结果呢???
A1 AAA,BBB
A2 null
A3 ABC,CBA
insert into tb select 'A1'
union all select 'A2'
union all select 'A3'
gocreate table tt(id varchar(10),[string] varchar(10))
insert into tt select 'A1' , 'AAA'
union all select 'A1' , 'BBB'
union all select 'A3' , 'ABC'
union all select 'A3' , 'CBA'
go
create function dbo.fc_str(@id varchar(10))
returns varchar(100)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+','+cast([string] as varchar) from tt where id=@id
return stuff(@sql,1,1,'')
end
goselect b.id,dbo.fc_str(a.id) as string from tt a,tb b where a.id=*b.id group by a.id,b.id order by b.iddrop table tb
drop table tt
drop function dbo.fc_str
create table A(id varchar(2))
insert A select 'A1'
insert A select 'A2'
insert A select 'A3'
insert B select 'A1','AAA'
insert B select 'A1','BBB'
insert B select 'A3','ABC'
insert B select 'A3','CBA'
gocreate function f_c(@id varchar(10))
returns varchar(100)
as
begin
declare @char varchar(100)
set @char=''
select @char=@char+name+',' from B where id=@id
return left(@char,len(@char)-1)
endgo
select c.id,d.name from a c left join
(select distinct id,name=dbo.f_c(id) from b) d
on c.id=d.iddrop table a,b
drop function f_c
/*结果id name
---- --------------------------
A1 AAA,BBB
A2 NULL
A3 ABC,CBA
*/
select b.id,dbo.fc_str(a.id) as string from tt a,tb b where a.id=*b.id group by a.id,b.id order by b.id
A2 A1 ST001 TT
A3 A3 ST001 CS
A4 A3 ST002 CS
A5 A5 ST005 TT
A5 ST007 WT查询出来的结果为以下,
aDFAS
A1 STOO1 CS,TT
A2 NULL NULL
A3 STOO1,STOO2 CS
A4 NULL NULL
A5 ST005,ST007 TT,WT
--建立測試環境
Create Table A (ID Varchar(10))
Insert A Select 'A1'
Union All Select 'A2'
Union All Select 'A3'
Union All Select 'A4'
Union All Select 'A5'
GO
--插入數據
Create Table B(
ID Varchar(10),
NO Varchar(10),
Name Varchar(10))
Insert B Select 'A1' , 'ST001', 'CS'
Union All Select 'A1' , 'ST001', 'TT'
Union All Select 'A3' , 'ST001', 'CS'
Union All Select 'A3' , 'ST002', 'CS'
Union All Select 'A5' , 'ST005', 'TT'
Union All Select 'A5' , 'ST007', 'WT'
GO
--建立函數
Create Function dbo.GetStr(@ID Varchar(10),@Flag Int)
Returns Varchar(100)
As
Begin
Declare @S Varchar(1000)
Set @S=''
If @Flag=1
Select @S=@S+','+NO from (Select Distinct ID,NO from B) T where ID=@ID
If @Flag=2
Select @S=@S+','+Name from B where ID=@ID
Return Stuff(@S,1,1,'')
End
GO
--測試
Select
ID,
dbo.GetStr(ID,1) As No,
dbo.GetStr(ID,2) As Name
from A
--刪除測試環境
Drop Table A,B
Drop Function dbo.GetStr
--結果
/*
ID No Name
A1 ST001 CS,TT
A2 NULL NULL
A3 ST001,ST002 CS,CS
A4 NULL NULL
A5 ST005,ST007 TT,WT
*/