有二张表A和B如下:
表A 表B
---------------- ----------------------------
A_fwbh B_fwbh B_fwczbh
--------------- ----------------------------
1 1 1-1
--------------- ----------------------------
2 1 1-2
--------------- ----------------------------
2 2-9
运用什么方法可以得到如下一个结果集:
---------------------------------------
1 1-1 1-2
2 2-9
表A 表B
---------------- ----------------------------
A_fwbh B_fwbh B_fwczbh
--------------- ----------------------------
1 1 1-1
--------------- ----------------------------
2 1 1-2
--------------- ----------------------------
2 2-9
运用什么方法可以得到如下一个结果集:
---------------------------------------
1 1-1 1-2
2 2-9
1 1-2
2 2-9这样的结果不一样吗。select A.A_fwbh ,B.B_fwczbh
from 表A as A,表B as B
where A.A_fwbh=B.B_fwbh
如果按楼上所写的会有三条记录
1 1-1
1 1-2
2 2-9
我就是想得到二条记录就可以了
1 1-1 1-2
2 2-9
如果没有SQL语句的话,有没有其他方法实现呢?
表2记录合并用SQL自定义函数!
Insert TableA Select 1
Union all Select 2
--TableB
Create Table TableB(B_fwbh Int,B_fwczbh Varchar(10))
Insert TableB Select 1,'1-1'
Union all select 1,'1-2'
Union all Select 2,'2-9'
--Create UDF
create function f_str(@B_fwbh int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+' '+B_fwczbh from TableB where B_fwbh = @B_fwbh
set @ret = stuff(@ret,1,1,'')
return @ret
end
go
--执行
Select A.A_Fwbh,B.B_Fwczbh
From TableA A
Left Join
(
select B_fwbh,B_fwczbh=dbo.f_str(B_fwbh)
from TableB
group by B_fwbh ) B
On A.A_Fwbh=B.B_fwbh