Here I have a table called Products:

Requirements: Whenever you buy three 'a' items, the cheapest of the three will be free; Whenever you buy two 'b' items, the cheapest of the two will be free. The results table should be like:

Explanation: For transaction_id 1, the total cost is: 350 250 100 100=800 (the items whose id == 3 and 6 have become free; For transaction_id 2, the total cost is: 400 60=460 (the item whose id == 8 has become free)

I have no clue about how to select all the free items.

CodePudding user response：

For MySQL 8.0 , you can use the ROW_NUMBER() window function as the following:

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

See a demo.

For older versions of MySQL, you can simulate the ROW_NUMBER() functionality as the following:

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

See a demo.

CodePudding user response：

According to your comments on my previous answer, you want to be able to exclude the cheapest N products from an X number of bought products for a specific type. i.e. Whenever you buy four 'a' items, the cheapest two of the four will be free.

consider the following query:

set @package_size_a=4; /* number of items should be bought from type 'a' to get the offer */
set @package_size_b=2; /* number of items should be bought from type 'b' to get the offer */
set @remove_from_a_cheapest=2; /* number of cheapest products to be excluded from type 'a' */
set @remove_from_b_cheapest=1; /* number of cheapest products to be excluded from type 'b' */
set @tid=null; /* reset row number to 1 whenever this value <> current transaction_id according to the selection order */
set @tp=null; /* reset row number to 1 whenever this value <> current type according to the selection order */
set @rn=0; /* row number set by the if statement inside the query */

select D.transaction_id, sum(D.discount_price) as price
from
   (
     select T.transaction_id, 
            case 
             when (T.type='a' and T.rn > @remove_from_a_cheapest) 
             or (T.type='b' and T.rn > @remove_from_b_cheapest) 
             then 0
             else T.price 
            end as discount_price
    from
        (
          select *, 
                 if(@tid<> transaction_id or 
                    @tp <> type or 
                    (type='a' and @rn=@package_size_a) or /* the row number exceeds the package_size_a value, then reset to 1 */
                    (type='b' and @rn=@package_size_b), /* the row number exceeds the package_size_b value, then reset to 1 */
                    @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  

The comments in the query explain how this query works.

If you have other types than (a and b), you can modify the query to include them.

Last thing, it's highly recommended to think about upgrading your MySQL version.

See a demo.