需求如下:
a表:
p_id status
1 0
2 0b表
rec_id pid purchase_qty receive_qty flag
1 1 10 10 0
2 1 1000 1000 0
3 2 25 10 1
4 2 25 25 0
想得到如下的结果:
1.如果b表中的purchase_qty=receive_qty,且b表中的pid关联a表中的p_id,当a表中所有在b表中的pid=1且purchase_qty与receive_qty相同时,更新a表中的status为1
2.如果B表中的flag为1的话,表示这条记录也完成了,不需要考虑purchase_qty=receive_qty这样的条件,也将a表中的status更新为1谢谢大家乐
a表:
p_id status
1 0
2 0b表
rec_id pid purchase_qty receive_qty flag
1 1 10 10 0
2 1 1000 1000 0
3 2 25 10 1
4 2 25 25 0
想得到如下的结果:
1.如果b表中的purchase_qty=receive_qty,且b表中的pid关联a表中的p_id,当a表中所有在b表中的pid=1且purchase_qty与receive_qty相同时,更新a表中的status为1
2.如果B表中的flag为1的话,表示这条记录也完成了,不需要考虑purchase_qty=receive_qty这样的条件,也将a表中的status更新为1谢谢大家乐
update @ta
set status=1
from @ta a,@tb b
where a.p_id=b.pid
and (purchase_qty=receive_qty or flag=1)??
exists(select 1 from b表 where A.pid=pid and (purchase_qty=receive_qty or flag=1))??
b.purchase_qty = b.receive_qty and b.pid = 1 then 1
when b.flag = 1 then 1
else t.status end
from a as t,b
where t.p_id = b.pid
update
a
set
status=1
from
a,b
where
b.purchase_qty=b.receive_qty and a.p_id=1 and a.p_id=b.pid and b.flag!=1update
a
set
status=1
where
b.flag=1
B WHERE A.PID=B.PID
AND (B.purchase_qty=B.receive_qty OR B.flag=1)
update a set status='1' from a
where not exists
(select 1 from b where a.pid=b.pid and purchase_qty<>receive_qty group by pid having sum(flag)=0)
这样不行啊,要b表中所有pid=1的记录的purchase_qty=receive_qty相等的时候才能跟新的
如果多加上a.p_id=1的条件的话,等于限定死了,所以不能加上这个条件,
其实需求就是当主表中的p_id在细表中的所有相关记录的purchase_qty=receive_qty时,就更新主表的状态或者当细表中有flag为1的条件时,就不用考虑purchase_qty=receive_qty的情况
UPDATE aa SET STATUS=1
FROM a aa INNER JOIN (SELECT * FROM b g WHERE
(SELECT COUNT(1) FROM b WHERE pid=g.pid AND flag=0 AND purchase_qty<>receive_qty)=0) bb
ON aa.p_id=bb.pid
(select count(*) from b where pid = t.p_id and pid = 1) =
(select count(*) from b where pid = t.p_id and pid = 1 and purchase_qty = receive_qty)
then 1
when b.flag = 1 then 1
else t.status end
from a as t,b
where t.p_id = b.pid?
declare @ta table (p_id int,status int)
insert into @ta
select 1,0 union all
select 2,0 union all
select 4,0--> 测试数据: @tb
declare @tb table (rec_id int,pid int,purchase_qty int,receive_qty int,flag int)
insert into @tb
select 1,1,10,10,0 union all
select 2,1,1000,1000,0 union all
select 3,2,25,10,1 union all
select 4,4,10,15,0 union all ---这个不等
select 5,4,25,25,0
update @ta
set status=1
from @ta a,(
select pid from @tb where pid not in(
select pid from @tb where flag!=1 and purchase_qty!=receive_qty
)
)t
where a.p_id=t.pid
---------------------->结果
select * from @ta
p_id status
----------- -----------
1 1
2 1
4 0(3 行受影响)
update a set status = case when
(select count(*) from b where pid = t.p_id) =
(select count(*) from b where pid = t.p_id and purchase_qty = receive_qty)
then 1
when (select count(*) from b where pid = t.p_id and flag = 1) > 0 then 1
else t.status
end
from a as t,b
where t.p_id = b.pid