2个简单的表合并!

CREATE TABLE #TB1

(

	ITEM_CODE varchar(14),

	DES nvarchar(10),

	WIP int,

	STOCK int

 );

 

INSERT #TB1

SELECT '21-00001-00010', '塑胶A', 500, 400 UNION ALL

SELECT '21-00002-00020', '塑胶B', 100, 0   UNION ALL

SELECT '21-00002-00030', '塑胶C', 0,   600;



CREATE TABLE #TB2

(

	ITEM_CODE varchar(14),

	DES nvarchar(10),

	PONumber int

 );

 

INSERT #TB2

SELECT '21-00001-00010', '塑胶A', 8000 UNION ALL

SELECT '21-00002-00020', '塑胶B', 300;





-------------------------------------------------------

SELECT A.*,PONumber=ISNULL(PONumber,0)

FROM #TB1 A LEFT JOIN #TB2 B

	ON A.ITEM_CODE=B.ITEM_CODE



--结果：



ITEM_CODE      DES        WIP         STOCK       PONumber

-------------- ---------- ----------- ----------- -----------

21-00001-00010 塑胶A        500         400         8000

21-00002-00020 塑胶B        100         0           300

21-00002-00030 塑胶C        0           600         0



(3 行受影响)

select m.* , isnull(n.PONumber , 0) PONumber  

from tb1 m left join tb2 n

on m.ITEM_CODE = n.ITEM_CODE

order by m.ITEM_CODE



select m.* , isnull(n.PONumber , 0) PONumber  

from tb1 m left join tb2 n

on m.ITEM_CODE = n.ITEM_CODE and m.DES = n.DES

order by m.ITEM_CODE,m.DES

select m.*,ishull(n.ponumber,0) ponumber

from tb1 m,tb2 n 

where m.item_code=n.item_code

select b1.*,isnull(b2.PONumber,0) PONumber

from tb1 b1

left join tb2 b2

on b1.ITEM_CODE=b2.ITEM_CODE