有这个一个表number 表示序号 bin:柜组 product商品 qty 数量
需要实现 相同bin 相同product qty取最大的值
不同bin 相同product qty取sum值。
number bin product qty
3 1001 100001 33
4 1002 100001 44
4 1002 100002 44
4 1002 100003 44
5 1002 100001 55
5 1002 100002 55
需要实现 相同bin 相同product qty取最大的值
不同bin 相同product qty取sum值。
number bin product qty
3 1001 100001 33
4 1002 100001 44
4 1002 100002 44
4 1002 100003 44
5 1002 100001 55
5 1002 100002 55
select * from tb t
where qty=(select max(qty) from tb where bin=t.bin and product=t.product))tt
group by product
select product,sum(qty) from (
select * from tb t
where not exists
(select 1 from tb where bin=t.bin and product=t.product and qty>t.qty))tt
group by product
product,
sum(qty) as qty
from
(select * from tb t where not exists(select 1 from tb where bin=bin and product=t.product and qty>t.qty)t
group by
product
select product,SUM(qty) from(
select bin,product,MAX(qty) as qty from TB group by bin,product
) t group by product