如题,在数据库中有三个字段id,name,Qty
1 A 100
2 A 300
3 A 100
4 A 100
5 A -100其中id是主键,如何写SQL 语句可以把 id=5的负数记录可 id=1,3,4中的任意一条值相等的正数数据相互抵消(删除)
使得最后数据是id,name,Qty
1 A 100
2 A 300
3 A 100
1 A 100
2 A 300
3 A 100
4 A 100
5 A -100其中id是主键,如何写SQL 语句可以把 id=5的负数记录可 id=1,3,4中的任意一条值相等的正数数据相互抵消(删除)
使得最后数据是id,name,Qty
1 A 100
2 A 300
3 A 100
select id from
(select a.id,b.id bid ,a.name,a.qty,b.name bname,b.qty bqty,row_number()over(partition by b.name,b.qty) rn from
(select id,name,qty from tb where qty>=0) a right join
(select id,name,qty from tb where qty<0) b on a.name=b.name and a.qty=abs(b.qty)) where rn=1 and id is not null) or id in (
select bid from
select a.id,b.id bid ,a.name,a.qty,b.name bname,b.qty bqty,row_number()over(partition by b.name,b.qty) rn from
(select id,name,qty from tb where qty>=0) a right join
(select id,name,qty from tb where qty<0) b on a.name=b.name and a.qty=abs(b.qty)) where rn=1 and id is null)没测试,但感觉是对的
group by id, name