求一个sql查询语句

Question

有两个表，结构如下
表1:table_mat1 (
    cmdid int --自动增长id
    matid int --原料档案ID
    matmaxnum int --原料最大允许数
    matcurnum int --原料当前数
)
表2:table_mat2 (
   cmdid int --自动增长ID
   matid int --原料档案ID
   mathasnum --原料当前现有总数
)
如何把表2的原料当前现有总数(mathasnum)分到表1的原料当前数(matcurnum)中，并且不让matcurnum超过matmaxnum如:
表1  cmdid matid matmaxnum matcurnum 
     1     m1    100       0
     2     m2    90        0
     3     m1    50        0
     4     m3    20        0
     5     m2    110       0
     6     m3    50        0表2 cmdid matid mathasnum
    10    m1    500
    11    m2    150
    12    m3    20如何把表2中的mathasnum数量update进表1的matcurnum中，并且需要下面这种结果
表1  cmdid matid matmaxnum matcurnum
     1     m1    100       100
     2     m2    90        90
     3     m1    50        50
     4     m3    20        20
     5     m2    110       60
     6     m3    50        0小弟想了N久没想出这种查询方法，期待答案，不胜感激

Answer 1

更正一下matid   int   --原料档案ID 
应为varchar(50)

Answer 2

想了一个很笨的方法--> 测试数据: [tta]
if object_id('[tta]') is not null drop table [tta]
go
create table [tta] (cmdid int,matid varchar(2),matmaxnum int,matcurnum int)
insert into [tta]
select 1,'m1',100,0 union all
select 2,'m2',90,0 union all
select 3,'m1',50,0 union all
select 4,'m3',20,0 union all
select 5,'m2',110,0 union all
select 6,'m3',50,0
--> 测试数据: [ttb]
if object_id('[ttb]') is not null drop table [ttb]
go
create table [ttb] (cmdid int,matid varchar(2),mathasnum int)
insert into [ttb]
select 10,'m1',500 union all
select 11,'m2',150 union all
select 12,'m3',20declare @m1 int,@m2 int,@m3 int,@mm1 int,@mm2 int,@mm3 int
set @m1=500
set @m2=150
set @m3=20
 
update tta
set matcurnum=case when @mm1>matmaxnum then matmaxnum else @mm1 end,@mm1=@m1,@m1=case when @m1>=matmaxnum then @m1-matmaxnum else @m1 end 
where matid='m1'
 
update tta
set matcurnum=case when @mm2>matmaxnum then matmaxnum else @mm2 end,@mm2=@m2,@m2=case when @m2>=matmaxnum then @m2-matmaxnum else @m2 end 
where matid='m2'update tta
set matcurnum=case when @mm3>matmaxnum then matmaxnum else @mm3 end,@mm3=@m3,@m3=case when @m3>=matmaxnum then @m3-matmaxnum else @m3 end 
where matid='m3'
select * from [tta] cmdid       matid matmaxnum   matcurnum
----------- ----- ----------- -----------
1           m1    100         100
2           m2    90          90
3           m1    50          50
4           m3    20          20
5           m2    110         60
6           m3    50          0(6 行受影响)

Answer 3

--测试数据
declare @TableB table
(
cmdid int,
matid varchar(10),
mathasnum int
)
declare @TableA table
(
cmdid int,
matid varchar(10),
matmaxnum int,
matcurnum int
)
insert into @TableA
select 1,'m1'  ,100,0 union all 
select 2,'m2'  ,90 ,0 union all 
select 3,'m1'  ,50 ,0 union all 
select 4,'m3'  ,20 ,0 union all 
select 5,'m2'  ,110,0 union all 
select 6,'m3'  ,50 ,0 insert into @TableB
select 10,'m1' ,500 union all 
select 11,'m2' ,150 union all 
select 12,'m3' ,20  --测试TSQL
update @TableA 
set matcurnum = 
case when A.matmaxnum <= (select B.mathasnum - 
(select isnull(sum(matmaxnum),0) SumA  from @TableA where A.matid = matid and A.cmdid > cmdid)  
from @TableB B where B.matid = A.matid ) then A.matmaxnum 
else  (select B.mathasnum - 
(select isnull(sum(matmaxnum),0) SumA  from @TableA where A.matid = matid and A.cmdid > cmdid)  
from @TableB B where B.matid = A.matid ) end
from @TableA A--Result
select * from @TableA
cmdid matid matmaxnum matcurnum
1 m1 100 100
2 m2 90 90
3 m1 50 50
4 m3 20 20
5 m2 110 60
6 m3 50 0

Answer 4

查询
---测试数据---
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([cmdid] int,[matid] varchar(2),[matmaxnum] int,[matcurnum] int)
insert [t1]
select 1,'m1',100,0 union all
select 2,'m2',90,0 union all
select 3,'m1',50,0 union all
select 4,'m3',20,0 union all
select 5,'m2',110,0 union all
select 6,'m3',50,0 union all
select 6,'m2',50,0
go
if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([cmdid] int,[matid] varchar(2),[mathasnum] int)
insert [t2]
select 10,'m1',500 union all
select 11,'m2',150 union all
select 12,'m3',20
go---查询---
select a.*,
case 
  when b.mathasnum-(select sum(c.matmaxnum) from t1 c where c.matid=a.matid and c.cmdid<=a.cmdid)>=0 
    then a.matmaxnum
  else
    case 
      when b.mathasnum-(select sum(c.matmaxnum) from t1 c where c.matid=a.matid and c.cmdid<a.cmdid)>0
        then b.mathasnum-(select sum(c.matmaxnum) from t1 c where c.matid=a.matid and c.cmdid<a.cmdid)
      else 0
    end
end as newNum
from t1 a join t2 b on a.matid=b.matid---结果---
cmdid       matid matmaxnum   matcurnum   newNum
----------- ----- ----------- ----------- -----------
1           m1    100         0           100
2           m2    90          0           90
3           m1    50          0           50
4           m3    20          0           20
5           m2    110         0           60
6           m3    50          0           0
6           m2    50          0           0(7 行受影响)

Answer 5

---更新---
update t1
set t1.matcurnum=b.newNum
from
(
select a.*,
case 
  when b.mathasnum-(select sum(c.matmaxnum) from t1 c where c.matid=a.matid and c.cmdid<=a.cmdid)>=0 
    then a.matmaxnum
  else
    case 
      when b.mathasnum-(select sum(c.matmaxnum) from t1 c where c.matid=a.matid and c.cmdid<a.cmdid)>0
        then b.mathasnum-(select sum(c.matmaxnum) from t1 c where c.matid=a.matid and c.cmdid<a.cmdid)
      else 0
    end
end as newNum
from t1 a join t2 b on a.matid=b.matid
) b
where t1.cmdid=b.cmdidselect * from t1/**
cmdid       matid matmaxnum   matcurnum
----------- ----- ----------- -----------
1           m1    100         100
2           m2    90          90
3           m1    50          50
4           m3    20          20
5           m2    110         60
6           m3    50          0
6           m2    50          0(7 行受影响)
**/