求取两张表的数据，谢谢，
表TB1
prodid month year no1
P01 5 12 3000
P02 5 12 2000
P03 6 12 2010
表TB2
prodid month year no2
P01 5 12 6000
P02 5 12 7000
P06 6 12 4000
希望得到结果：
prodid month year no1 no2
P01 5 12 3000 6000
P02 5 12 2000 7000
P03 6 12 2010 0
P06 6 12 0 4000
------解决方案--------------------
select isnull(a.prodid,b.prodid) prodid, isnull(a.month,b.month) month,
isnull(a.year,b.year) year,isnull(no1,0) no1,isnull(no2,0) no2
from tb1 as a full join tb2 as b on a.prodid=b.prodid and a.month=b.month and a.year=b.year
------解决方案--------------------
create table TB1
(
id varchar(20) not null,
[month] int null,
[year] int null,
no1 varchar(20) null
)
create table TB2
(
id varchar(20) not null,
[month] int null,
[year] int null,
no2 varchar(20) null
)
insert into tb1
select 'po1',5,12,3000 union
select 'po2',5,12,2000 union
select 'po3',6,12,2010
insert into tb2
select 'po1',5,12,6000 union
select 'po2',5,12,7000 union
select 'po6',6,12,4000
select isnull(a.id,b.id) id, isnull(a.month,b.month) month,
isnull(a.year,b.year) year,isnull(a.no1,0) no1,isnull(b.no2,0) no2
from TB1 a full join TB2 b
on a.id=b.id
------解决方案--------------------
--CREATE TABLE TB1
-- (
-- id VARCHAR(20) NOT NULL ,
-- [month] INT NULL ,
-- [year] INT NULL ,
-- no1 VARCHAR(20) NULL
-- )
--CREATE TABLE TB2
-- (
-- id VARCHAR(20) NOT NULL ,
-- [month] INT NULL ,
-- [year] INT NULL ,
-- no2 VARCHAR(20) NULL
-- )
--INSERT INTO tb1
-- SELECT 'po1' ,
-- 5 ,
-- 12 ,