按累计列数量限制

vs3odd8k  于 2021-06-18  发布在  Mysql
关注(0)|答案(2)|浏览(360)

假设我有一个有3列的表:id,row\u type,row\u score
我想选择第一(或最新)行,但根据获取的id的累积分数限制选择
例子 table ```
id | row_type | row_score
1 a 1
2 a 1
3 b 2
4 c 3
5 a 1
6 b 2
7 a 1
...

第一行成绩,累计得分限制为4分:

id | row_type | row_score
1 a 1
2 a 1
3 b 2

pw9qyyiw

pw9qyyiw1#

这将为您提供所需的结果:

select t1.id, t1.row_type,t1.row_score, SUM(t2.row_score) as sum
from table t1
inner join table t2 
on t1.id >= t2.id
group by t1.id, t1.row_type,t1.row_score
having SUM(t2.row_score)<=4
order by t1.id

谢谢,
罗汉霍达卡

mklgxw1f

mklgxw1f2#

此查询应该满足您的要求。它使用一个变量来保存一个累积分数,然后在 HAVING 子句来限制返回的行:

SELECT t1.*, @cum_score := @cum_score + row_score AS cum_score
FROM table1 t1
JOIN (SELECT @cum_score := 0) c
HAVING cum_score <= 4
ORDER BY cum_score

输出:

id  row_type    row_score   cum_score
1   a           1           1
2   a           1           2
3   b           2           4

sqlfiddle演示

相关问题