请教一SQL语句 ---在线等
表Ta
TaId TaName ProductId
1 Test1 P1
2 Test2 P2
3 Test3 P3
表Tb
TbId TbName ProductId
1 T1 P1
2 T2 P2
3 T3 P4
要生成以下结果
TaId TaName ProductId TbId TbName
1 Test1 P1 1 T1
2 Test2 P2 2 T2
3 Test3 P3
P4 3 T3
请问SQL语句该怎么写.
谢谢
[解决办法]
select * , isnull(tb.tbid, ' ') , isnull(tb.tbname, ' ') from ta
full join tb on ta.productid = tb.productid
[解决办法]
--生成测试数据
declare @Ta table(TaId int,TaName varchar(10),ProductId varchar(10))
insert into @Ta select 1, 'Test1 ', 'P1 '
insert into @Ta select 2, 'Test2 ', 'P2 '
insert into @Ta select 3, 'Test3 ', 'P3 '
declare @Tb table(TbId int,TbName varchar(10),ProductId varchar(10))
insert into @Tb select 1, 'T1 ' , 'P1 '
insert into @Tb select 2, 'T2 ' , 'P2 '
insert into @Tb select 3, 'T3 ' , 'P4 '
--执行查询
select
a.TaId,
a.TaName,
isnull(a.ProductId,b.ProductId) as ProductId,
b.TbId,
b.TbName
from
@Ta a
full outer join
@Tb b
on
a.ProductId=b.ProductId
order by
isnull(a.ProductId,b.ProductId)
--查询所输出的结果
/*
TaId TaName ProductId TbId TbName
----------- ---------- ---------- ----------- ----------
1 Test1 P1 1 T1
2 Test2 P2 2 T2
3 Test3 P3 NULL NULL
NULL NULL P4 3 T3
*/
