源数据和DDL
CREATE TABLE T
([Order_NO] varchar(10), [MO_NO] varchar(10), [DEP] varchar(3), [PRD_NO] varchar(12), [QTY] int, [STD_QTY] numeric, [QTY_DIF] numeric);
INSERT INTO T
([Order_NO], [MO_NO], [DEP], [PRD_NO], [QTY], [STD_QTY], [QTY_DIF])
VALUES
('SO17110026', 'MO17110824', 'MO2', '3CA002-00120', 40681, 40380.48, 300.52),
('SO17110026', 'MO17110824', 'MO2', '3CA002A00110', 41125, 40380.48, 744.52),
('SO17110026', 'MO17110825', 'MO1', '403010060002', 40381, 40380.48, 0.52),
('SO17110026', 'MO17110825', 'MO1', '601001000734', 69, 68.102, 0.898),
('SO17110026', 'MO17110826', 'MO1', '601002000004', 2862, 2861.4858, 0.5142),
('SO17110026', 'MO17110826', 'MO1', '601005000127', 1499, 1498.6446, 0.3554),
('SO17110026', 'MO17110826', 'MO1', '601007000001', 69, 68.102, 0.898),
('SO17110029', 'MO17110825', 'MO1', '40107008CM01', 238010, 237658.3374, 351.6626),
('SO17110029', 'MO17110825', 'MO1', '40107008CM33', 110000, 109149.843, 850.157)
;
Order_NO | MO_NO | DEP | PRD_NO | QTY | STD_QTY | QTY_DIF
:--------- | :--------- | :-- | :----------- | -----: | :------ | :------
SO17110026 | MO17110824 | MO2 | 3CA002-00120 | 40681 | 40380 | 301
SO17110026 | MO17110824 | MO2 | 3CA002A00110 | 41125 | 40380 | 745
SO17110026 | MO17110825 | MO1 | 403010060002 | 40381 | 40380 | 1
SO17110026 | MO17110825 | MO1 | 601001000734 | 69 | 68 | 1
SO17110026 | MO17110826 | MO1 | 601002000004 | 2862 | 2861 | 1
SO17110026 | MO17110826 | MO1 | 601005000127 | 1499 | 1499 | 0
SO17110026 | MO17110826 | MO1 | 601007000001 | 69 | 68 | 1
SO17110029 | MO17110825 | MO1 | 40107008CM01 | 238010 | 237658 | 352
SO17110029 | MO17110825 | MO1 | 40107008CM33 | 110000 | 109150 | 850
预期结果
Order_NO | MO_NO | DEP | PRD_NO | QTY | STD_QTY | QTY_DIF
:--------- | :--------- | :--- | :----------- | -----: | :------ | :------
SO17110026 | MO17110824 | MO2 | 3CA002-00120 | 40681 | 40380 | 301
null | null | null | 3CA002A00110 | 41125 | 40380 | 745
null | MO17110825 | MO1 | 403010060002 | 40381 | 40380 | 1
null | null | null | 601001000734 | 69 | 68 | 1
null | MO17110826 | MO1 | 601002000004 | 2862 | 2861 | 1
null | null | null | 601005000127 | 1499 | 1499 | 0
null | null | null | 601007000001 | 69 | 68 | 1
SO17110029 | MO17110825 | MO1 | 40107008CM01 | 238010 | 237658 | 352
null | null | null | 40107008CM33 | 110000 | 109150 | 850