22,301
社区成员




(物料编码) (物料名称) (数量1) (数量2)
ITEM_CODE DES WIP STOCK
21-00001-00010 塑胶A 500 400
21-00002-00020 塑胶B 100 0
21-00002-00030 塑胶C 0 600
......
(物料编码) (物料名称) (PO数量)
ITEM_CODE DES PONumber
21-00001-00010 塑胶A 8000
21-00002-00020 塑胶B 300
......
(物料编码) (物料名称) (数量1) (数量2) (PO数量)
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
......
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