--建立测试环境 IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb ( id int identity, 数量 INT, 仓库 INT, CONSTRAINT PK_TB PRIMARY KEY (id) ) GO INSERT TB SELECT 20 ,1 UNION ALL SELECT 30 ,2 UNION ALL SELECT 50 ,3 UNION ALL SELECT 80 ,3 --查询 SELECT * FROM TB T WHERE (SELECT SUM(数量) FROM TB WHERE ID<=T.ID)<=100 --结果 /* (4 行受影响) id 数量 仓库 ----------- ----------- ----------- 1 20 1 2 30 2 3 50 3(3 行受影响) */
--> 生成测试数据表: [tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb] ([id] [int],[数量] [int],[仓库] [int]) INSERT INTO [tb] SELECT '1','20','1' UNION ALL SELECT '2','30','2' UNION ALL SELECT '3','80','3'--SELECT * FROM [tb] -->SQL查询如下: IF object_id('p_test') >0 DROP PROC p_test GO CREATE PROC p_test @sumsl INT AS SELECT id, 数量=CASE WHEN 数量-(sumsl-@sumsl)<=0 THEN 0 WHEN sumsl-@sumsl<=0 THEN 数量 ELSE 数量-(sumsl-@sumsl) END, [仓库] FROM ( SELECT *,sumsl=(SELECT SUM([数量]) FROM tb WHERE id<=t.id) FROM tb t ) t GO EXEC p_test 100 /* id 数量 仓库 ----------- ----------- ----------- 1 20 1 2 30 2 3 50 3(3 行受影响) */
declare @a table(id INT, s INT,c INT) insert @a select 1,20,1 union all select 2,30,2 union all select 3,80,3 union all select 4,20,4 SELECT id,s=CASE WHEN l>0 THEN s ELSE s+l END,c FROM ( SELECT *,100-(SELECT SUM(s) FROM @a WHERE id<=a.id) l FROM @a a )aa WHERE CASE WHEN l>0 THEN s ELSE s+l END >0--result /*id s c ----------- ----------- ----------- 1 20 1 2 30 2 3 50 3(所影响的行数为 3 行)*/
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb
GO
CREATE TABLE tb
(
id int identity,
数量 INT,
仓库 INT,
CONSTRAINT PK_TB PRIMARY KEY (id)
)
GO
INSERT TB
SELECT 20 ,1 UNION ALL
SELECT 30 ,2 UNION ALL
SELECT 50 ,3 UNION ALL
SELECT 80 ,3
--查询
SELECT * FROM TB T
WHERE (SELECT SUM(数量) FROM TB WHERE ID<=T.ID)<=100
--结果
/*
(4 行受影响)
id 数量 仓库
----------- ----------- -----------
1 20 1
2 30 2
3 50 3(3 行受影响)
*/
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([id] [int],[数量] [int],[仓库] [int])
INSERT INTO [tb]
SELECT '1','20','1' UNION ALL
SELECT '2','30','2' UNION ALL
SELECT '3','80','3'--SELECT * FROM [tb]
-->SQL查询如下:
IF object_id('p_test') >0
DROP PROC p_test
GO
CREATE PROC p_test
@sumsl INT
AS
SELECT id,
数量=CASE
WHEN 数量-(sumsl-@sumsl)<=0 THEN 0
WHEN sumsl-@sumsl<=0 THEN 数量
ELSE 数量-(sumsl-@sumsl)
END,
[仓库]
FROM (
SELECT *,sumsl=(SELECT SUM([数量]) FROM tb WHERE id<=t.id)
FROM tb t
) t
GO
EXEC p_test 100
/*
id 数量 仓库
----------- ----------- -----------
1 20 1
2 30 2
3 50 3(3 行受影响)
*/
insert @a select 1,20,1
union all select 2,30,2
union all select 3,80,3
union all select 4,20,4
SELECT id,s=CASE WHEN l>0 THEN s ELSE s+l END,c FROM
(
SELECT *,100-(SELECT SUM(s) FROM @a WHERE id<=a.id) l FROM
@a a
)aa
WHERE CASE WHEN l>0 THEN s ELSE s+l END >0--result
/*id s c
----------- ----------- -----------
1 20 1
2 30 2
3 50 3(所影响的行数为 3 行)*/