id 数量 仓库
 1  20    1
 2  30    2
 3  80    3
 
如何使用sql存储过程查询出按照编号正序数量累加值和=100的那一部分
体现结果如下
 id 数量 仓库
 1  20    1
 2  30    2
 3  50    3如何写存储过程?请高手指教!

解决方案 »

  1.   

    --建立测试环境
    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 行受影响)
    */
      

  2.   

    --> 生成测试数据表: [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 行受影响)
    */
      

  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 行)*/