22,210
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('t1') IS NOT NULL DROP TABLE t1
IF OBJECT_ID('t2') IS NOT NULL DROP TABLE t2
GO
CREATE TABLE t1(
pid INT PRIMARY KEY,
num INT
);
INSERT INTO t1 VALUES (300045,12);
CREATE TABLE t2(
t2Id INT IDENTITY(1,1) PRIMARY KEY,
pid INT,
rNum INT
)
INSERT INTO t2 VALUES (300045,3);
INSERT INTO t2 VALUES (300045,6);
INSERT INTO t2 VALUES (300045,2);
INSERT INTO t2 VALUES (300045,8);
--以上为测试表及测试数据
SELECT
t2.pid
,t2.rNum
,SUM(rNum) OVER(ORDER BY t2Id) AS total
,CASE WHEN t1.num - SUM(rNum) OVER(ORDER BY t2Id)>0 THEN t1.num - SUM(rNum) OVER(ORDER BY t2Id) ELSE 0 END AS inventory
,CASE WHEN SUM(rNum) OVER(ORDER BY t2Id)>t1.num THEN 'no' ELSE 'yes' END AS remark
FROM t2 INNER JOIN t1 ON t1.pid=t2.pid