日期:2014-05-17 浏览次数:20557 次
数据不具有代表性,就你上面的数据
DECLARE @M TABLE(JanCD VARCHAR(20),ItemCD VARCHAR(20))
INSERT @M SELECT 'J0001','I0001'
UNION ALL SELECT 'J0002','I0002'
DECLARE @A TABLE(COMCD VARCHAR(20),STOCD VARCHAR(20), JanCD VARCHAR(20))
INSERT @A SELECT '10000','SSSSS','J0001'
UNION ALL SELECT '10000','ZZZZZ','J0002'
DECLARE @B TABLE(COMCD VARCHAR(20),STOCD VARCHAR(20), ItemCD VARCHAR(20))
INSERT @B SELECT '10000','SSSSS','I0001'
UNION ALL SELECT '11111','BBBBB','I0001'
UNION ALL SELECT '10000','ZZZZZ','I0002'
UNION ALL SELECT '22222','XXXXX','I0002'
SELECT COALESCE(A.COMCD,B.COMCD) COMCD,
COALESCE(A.STOCD,B.STOCD) STOCD,
A.JanCD,
B.ItemCD
FROM @A A FULL JOIN @B B
ON A.COMCD=B.COMCD AND A.STOCD=B.STOCD
LEFT JOIN @M M ON A.JanCD=m.JanCD AND B.ItemCD=M.ItemCD
--result
/*COMCD STOCD JanCD ItemCD
-------------------- -------------------- -------------------- --------------------
10000 SSSSS J0001 I0001
11111 BBBBB NULL I0001
10000 ZZZZZ J0002 I0002
22222 XXXXX NULL I0002
(