SQL求助

Question

求助循环表达式 要在SQL server 数据里完成如下数据啊：
用SQL求缺料数量，tabe1 订单号，物料、物料名称、计划用量
                 tabe1 物料、物料名称、库存数量  数量如下表
数据库表 table1 字段 num, itemid, itemname, JHSL,  QLSL
                     1       A01     A        10      
                     1       B01     B        10      
                     2       A01     A        10       
                     2       B01     B        10       
                     3       A01     A        10       
                     3       B01     B        10       
                     4       A01     A        10       
         table2 字段 itemid, itemname, KCSL
                      A01     A        15   
                      B01     B        10   
如何把缺料数量QLSL=KCSL-JHSL  
得到如下表啊
数据库表 table3 字段 num, itemid, itemname, JHSL,  QLSL
                     1       A01     A        10     0 
                     1       B01     B        10     0
                     2       A01     A        10     5  
                     2       B01     B        10     10  
                     3       A01     A        10     10  
                     3       B01     B        10     10  
                     4       A01     A        10     10

Answer 1

with cte as
(
    select a.num,a.itemid,a.itemname,a.jhsl,(select sum(a.qlsl) from table1 where itemid = a.itemid and id <= a.id) - b.kcsl as qlsl
    from table1 a left join table2 b on a.itemid = b.itemid
)select num,itemid,itemname,hsl,(case when qlsl <= 0 then 0 else qlsl end)qlsl
from cte

Answer 2

update a set QLSL=(case when b.KCSL>=(select sum(JHSL) from tb where itemid=a.itemid and num<=a.num) then 0 
when else a.JHSL end)
from tb1 a,tb2 b where a.itemid=b.itemid

Answer 3

create table t1(num int, itemid varchar(10), itemname varchar(10), JHSL int, QLSL int)
insert into t1 values(1 ,'A01', 'A', 10,null)   
insert into t1 values(1 ,'B01', 'B', 10,null)   
insert into t1 values(2 ,'A01', 'A', 10,null)
insert into t1 values(2 ,'B01', 'B', 10,null)
insert into t1 values(3 ,'A01', 'A', 10,null)
insert into t1 values(3 ,'B01', 'B', 10,null)
insert into t1 values(4 ,'A01', 'A', 10,null)
create table t2(itemid varchar(10), itemname varchar(10), KCSL int)
insert into t2 values('A01', 'A', 15)
insert into t2 values('B01', 'B', 10)
go--查询
select t.num , t.itemid , t.itemname , t.JHSL , QLSL = (
case when (select sum(JHSL) from t1 where itemid = t.itemid and itemname = t.itemname and num <= t.num) <= m.KCSL then 0 
     when (select sum(JHSL) from t1 where itemid = t.itemid and itemname = t.itemname and num <= t.num) - m.KCSL <= t.JHSL then 
          t.JHSL - ((select sum(JHSL) from t1 where itemid = t.itemid and itemname = t.itemname and num <= t.num) - m.KCSL )
     else t.JHSL
end)
from t1 t , t2 m
where t.itemid = m.itemid and t.itemname = m.itemname--更新
update t1 
set QLSL = (
case when (select sum(JHSL) from t1 where itemid = t.itemid and itemname = t.itemname and num <= t.num) <= m.KCSL then 0 
     when (select sum(JHSL) from t1 where itemid = t.itemid and itemname = t.itemname and num <= t.num) - m.KCSL <= t.JHSL then 
          t.JHSL - ((select sum(JHSL) from t1 where itemid = t.itemid and itemname = t.itemname and num <= t.num) - m.KCSL )
     else t.JHSL
end)
from t1 t , t2 m
where t.itemid = m.itemid and t.itemname = m.itemnameselect * From t1drop table t1 , t2  /*
num         itemid     itemname   JHSL        QLSL        
----------- ---------- ---------- ----------- ----------- 
1           A01        A          10          0
1           B01        B          10          0
2           A01        A          10          5
2           B01        B          10          0
3           A01        A          10          10
3           B01        B          10          10
4           A01        A          10          10（所影响的行数为 7 行）*/

Answer 4

create table t1(num int, itemid varchar(10), itemname varchar(10), JHSL int, QLSL int)
insert into t1 values(1 ,'A01', 'A', 10,null)   
insert into t1 values(1 ,'B01', 'B', 10,null)   
insert into t1 values(2 ,'A01', 'A', 10,null)
insert into t1 values(2 ,'B01', 'B', 10,null)
insert into t1 values(3 ,'A01', 'A', 10,null)
insert into t1 values(3 ,'B01', 'B', 10,null)
insert into t1 values(4 ,'A01', 'A', 10,null)
create table t2(itemid varchar(10), itemname varchar(10), KCSL int)
insert into t2 values('A01', 'A', 15)
insert into t2 values('B01', 'B', 10)
go--查询
select t.num , t.itemid , t.itemname , t.JHSL , QLSL = (
case when (select sum(JHSL) from t1 where itemid = t.itemid and itemname = t.itemname and num <= t.num) <= m.KCSL then 0 
     when (select sum(JHSL) from t1 where itemid = t.itemid and itemname = t.itemname and num <= t.num) - m.KCSL < t.JHSL then 
          t.JHSL - ((select sum(JHSL) from t1 where itemid = t.itemid and itemname = t.itemname and num <= t.num) - m.KCSL )
     else t.JHSL
end)
from t1 t , t2 m
where t.itemid = m.itemid and t.itemname = m.itemname--更新
update t1 
set QLSL = (
case when (select sum(JHSL) from t1 where itemid = t.itemid and itemname = t.itemname and num <= t.num) <= m.KCSL then 0 
     when (select sum(JHSL) from t1 where itemid = t.itemid and itemname = t.itemname and num <= t.num) - m.KCSL < t.JHSL then 
          t.JHSL - ((select sum(JHSL) from t1 where itemid = t.itemid and itemname = t.itemname and num <= t.num) - m.KCSL )
     else t.JHSL
end)
from t1 t , t2 m
where t.itemid = m.itemid and t.itemname = m.itemnameselect * From t1drop table t1 , t2  /*
num         itemid     itemname   JHSL        QLSL        
----------- ---------- ---------- ----------- ----------- 
1           A01        A          10          0
1           B01        B          10          0
2           A01        A          10          5
2           B01        B          10          10
3           A01        A          10          10
3           B01        B          10          10
4           A01        A          10          10（所影响的行数为 7 行）
*/