两个表连接查询
a表
id 金额 科目编号
1 100 1001
2 200 1001
b表
id 金额 科目编号
3 100 1001
4 300 1001
5 450 1001
结果要
a金额 科目编号 b金额
100 1001 100
200 1001 300
0 1001 450
[解决办法]
drop table a,b
go
create table a(id int, 金额 int,科目编号 int)
insert into a
select 1 ,100, 1001 union all
select 2 ,200, 1001
create table b(id int, 金额 int, 科目编号 int)
insert into b
select 3 ,100, 1001 union all
select 4 ,300, 1001 union all
select 5 ,450, 1001
go
select isnull(a.金额,0) as 金额,b.科目编号,b.金额
from
(
select *,
ROW_NUMBER() over(order by id) rownum
from b
)b
left join a
on a.科目编号 = b.科目编号 and
a.id = b.rownum
/*
金额科目编号金额
1001001100
2001001300
01001450
*/
Select
ISNull(t2.金额 ,0) a金额
,ISnull(t1.科目编号,t2.科目编号) 科目编号
,ISNull(t1.金额 ,0) b金额
From
(
Select * From (row_number() Over (Order By B.id Asc)BRowNum
FROM B )t1
FULL JOIN
(
select *, (row_number() Over (Order By A.id Asc) ARowNum
From A
)t2
On t1.BRowNum = t2.ARowNum
