虽然不太美观,但我认为这样做可以完成任务,也许可以成为更简便的基础。请注意,我使用“伪”INNER JOIN只是为了第一次初始化一些变量--它没有其他作用。
SELECT ID,
supplier,
qty,
cumulative_qty
FROM
(
SELECT
ID,
supplier,
qty,
@cumulative_quantity := if (@sup <> supplier, qty, @cumulative_quantity + qty) as cumulative_qty,
@reached_five := if (@cumulative_quantity < 5, 0, if (@sup <> supplier, 1, @reached_five + 1)) as reached_five,
@sup := if(@sup <> supplier, supplier, @sup) as sup
FROM
(
SELECT *
FROM `sample_table`
ORDER BY supplier, ID DESC
) reverse_order_by_id
INNER JOIN
(
SELECT @cumulative_quantity := 0, @sup := 0, @reached_five := 0
) only_here_to_initialize_variables
) t_alias
where reached_five <= 1