当然,用几个变量,先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 ------解决方案-------------------- T1和T2是1对1还是1对多?
要是1对1:
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&nbs