如何实现"买X件送Y件免费功能"

Question

这里有一个名为产品的表：

id
1		20		3		4		5		6		7		8		9

对于MySQL 8.0+，可以使用ROW_NUMBER()窗口函数如下：

select transaction_id, sum(discount_price) as price
from
(
select transaction_id, 
case 
when (type='a' and row_number() over (partition by transaction_id, type order by price desc) % 3=0) 
or (type='b' and row_number() over (partition by transaction_id, type order by price desc) % 2=0) 
then 0
else price 
end as discount_price
from table_name
) T
group by transaction_id
order by transaction_id

查看演示。

对于旧版本的MySQL，可以模拟ROW_NUMBER()功能如下：

set @tid=null;
set @tp=null;
set @rn=0;
select D.transaction_id, sum(D.discount_price) as price
from
(
select T.transaction_id, 
case 
when (T.type='a' and T.rn % 3=0) 
or (T.type='b' and T.rn % 2=0) 
then 0
else T.price 
end as discount_price
from
(
select *, 
if(@tid<> transaction_id or @tp <> type,@rn:=1,@rn:=@rn+1) rn, 
@tid:=transaction_id, @tp:= type
from table_name
order by transaction_id,type, price desc
) T
) D
group by D.transaction_id
order by D.transaction_id

查看演示。