我有个A表,有个B表
A表有字段prid,money1
B表有字段prid, money2
有可能A表的一条数据对应B表的多条数据
那么显示结果为
prid,money1,prid,money2
1 50 1 20
1 30
1 40
有可能A表多条数据,B表一条数据或者两条数据那么显示
prid,money1,prid,money2
1 50 1 20
1 60 1 10
1 30
1 40
怎么用一条语句把这两张表关联起来啊
A表有字段prid,money1
B表有字段prid, money2
有可能A表的一条数据对应B表的多条数据
那么显示结果为
prid,money1,prid,money2
1 50 1 20
1 30
1 40
有可能A表多条数据,B表一条数据或者两条数据那么显示
prid,money1,prid,money2
1 50 1 20
1 60 1 10
1 30
1 40
怎么用一条语句把这两张表关联起来啊
(
select row_number() over(partition by prid order by getdate()) no,* from A
), cte2 as
(
select row_number() over(partition by prid order by getdate()) no,* from B
)select a.prid,a.money1,b.prid,b.money2
from cte1 a full join cte2 b on a.no=b.no and a.prid=b.prid
--sql2000
select rn=identity(int,1,1),* into #cte1 from A
select rn=identity(int,1,1),* into #cte2 from Aselect a.prid,a.money1,b.prid,b.money2
from (select no=(select count(1) from #cte1 where prid=a.prid and rn<=a.rn),prid,money1
from #cte1 a) a
full join (select no=(select count(1) from #cte2 where prid=a.prid and rn<=a.rn),prid,money2
from #cte2 a) b
on a.no=b.no and a.prid=b.prid