求取2张表的数据
求取两张表的数据，谢谢，
表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 ,
-- 3000
-- UNION ALL
-- SELECT 'po2' ,
-- 5 ,
-- 12 ,
-- 2000
-- UNION ALL
-- SELECT 'po3' ,
-- 6 ,
-- 12 ,
-- 2010
--INSERT INTO tb2
-- SELECT 'po1' ,
-- 5 ,
-- 12 ,
-- 6000
-- UNION ALL
-- SELECT 'po2' ,
-- 5 ,
-- 12 ,
-- 7000
-- UNION ALL
-- SELECT 'po6' ,
-- 6 ,
-- 12 ,
-- 4000
SELECT CASE WHEN a.id IS NULL THEN b.id ELSE a.id
END id ,
CASE WHEN a.MONTH IS NULL THEN b.MONTH ELSE a.MONTH
END MONTH ,
CASE WHEN a.year IS NULL THEN b.YEAR ELSE a.YEAR
END year ,
ISNULL(a.no1, 0) no1 ,
ISNULL(b.no2, 0) no2
FROM tb1 a
FULL JOIN tb2 b ON a.id = b.id
AND a.MONTH = B.MONTH
/*
id MONTH year no1 no2
-------------------- ----------- ----------- -------------------- --------------------
po1 5 12 3000 6000
po2 5 12 2000 7000
po3 6 12 2010 0
po6 6 12 0 4000
(4 行受影响)
*/
