怎么用一个简单的语句来完成关联表修改
有一个表T1,里面有倆个字段MainID和STATUS,表示ID和它的状态
另一个表T2,里面四个字段:MainID,DetailID;InQuantity和OutQuantity。后两个是数字,表示进出数量。InQuantity总是>0的。DetailID是明细ID。归属于某个MainID下面
现在我想要执行一个sql语句,对于某MainID,如果
OutQuantity全部=0,则T1的STATUS=1,就是一个都没出
OutQuantity全部=InQuantity,则T1的STATUS=3,就是全出光了
OutQuantity处于两者之间的,则T1的STATUS=2,出了部分
当然,用几个变量,先select count(*) where OutQuantity>0之类的来判断,很简单。
但是能不能在一个语句里就搞定?
类似 update T1 set STATUS=case when……这样的?
[解决办法]
UPDATE t1
SET t1.[status]=CASE WHEN b.OutQuantity=0 THEN 1 WHEN OutQuantity=InQuantity THEN 3 ELSE THEN 2 END
FROM t1 INNER JOIN (SELECT mainid,SUM(OutQuantity)OutQuantity,SUM(InQuantity)InQuantity FROM t2 GROUP BY mainid) t2 ON t1.mainid=t2.mainid
update T1 set STATUS= case when T2.OutQuantity=0 then 1 case when T2.OutQuantity=T2.InQuantity then 3 else 2 end
from T1,T2 where T1.MainID=T2.MainID
update t1
set status = case when t2.OutQuantity = 0 then 1
when t2.OutQuantity = t2.InQuantity then 3
when t2.OutQuantity between 0 and t2.InQuantity then 2
end
from t1 t
inner join
(
select MainID,SUM(InQuantity) as InQuantity,
sum(OutQuantity) as OutQuantity
from t2
group by MainID
)t2
on t1.MainID = t2.MainID