|
- USE tempdb
- GO
- IF OBJECT_ID('Test1') IS NOT NULL
- DROP TABLE Test1;
- GO
- CREATE TABLE Test1
- (
- 物料代码 CHAR(1),
- 在库数量 INT
- );
- GO
- INSERT INTO Test1 VALUES('A',1000);
- INSERT INTO Test1 VALUES('B',50);
- INSERT INTO Test1 VALUES('C',10);
- GO
- IF OBJECT_ID('Test2') IS NOT NULL
- DROP TABLE Test2;
- GO
- CREATE TABLE Test2
- (
- 生产代码 VARCHAR(6),
- 物料代码 CHAR(1),
- 需求量 INT
- );
- GO
- INSERT INTO Test2 VALUES('201601','A',300);
- INSERT INTO Test2 VALUES('201601','B',3);
- INSERT INTO Test2 VALUES('201602','A',400);
- INSERT INTO Test2 VALUES('201602','B',4);
- INSERT INTO Test2 VALUES('201602','C',1);
- INSERT INTO Test2 VALUES('201603','B',26);
- INSERT INTO Test2 VALUES('201603','C',12);
- GO
- ----------------------------------------
- SELECT * FROM TEST1;
- SELECT * FROM TEST2;
- SELECT A.*,B.在库数量 + A.需求量 - A.累积用量 AS 可用数量
- FROM
- (
- SELECT *,
- (
- SELECT SUM(需求量)
- FROM Test2 AS T2
- WHERE T2.生产代码 <= T1.生产代码
- AND T1.物料代码 = T2.物料代码
- ) AS 累积用量
- FROM Test2 AS T1
- ) AS A
- JOIN Test1 AS B
- ON A.物料代码 = B.物料代码
- ----------------------------------------
- /*
- 生产代码 物料代码 需求量 累积用量 可用数量
- 201601 A 300 300 1000
- 201601 B 3 3 50
- 201602 A 400 700 700
- 201602 B 4 7 47
- 201602 C 1 1 10
- 201603 B 26 33 43
- 201603 C 12 13 9
- */
复制代码 |
|