怎么用一个简单的语句来完成关联表修改

[xuguv]

有一个表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

[LongRui888]

update t1
set status = case when t2.OutQuantity = 0 then 1
                  when t2.OutQuantity = t2.InQuantity then 2
                  when t2.OutQuantity between 0 and t2.InQuantity then 3
             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

[LongRui888]

修改一下：

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

[Yole]

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 

要是1对多： 就需要对T2进行一下处理了，按规则取最大、最小还是取和~~

[xuguv]

回5L，是1对多。

[LongRui888]

引用 6 楼 xuguv 的回复:

回5L，是1对多。

试试这个，应该可以的：

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

[山寨DBA]

可以用merge试试