一些有意思的sql

create temporary table tmp_table
select *, ROW_NUMBER() over (order by diff) as rank_num
from (SELECT *, sum(num) over (order by overdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - 18 AS diff
FROM medical_goods
WHERE item_id = 7
AND overdate > now()) tv;

select rank_num
into @target_num
from tmp_table
where diff >= 0
limit 0,1;

select *
from tmp_table
where rank_num <= @target_num;

drop temporary table if exists tmp_table;

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT
@sum := @sum + num AS cumulative_sum,
num
FROM
medical_goods,
(SELECT @sum := 0) AS user_defined_vars
ORDER BY overdate