返回总和小于给定值的行 [英] return rows where sum on a field less than a given value

问题描述

我对 MySQL 的了解是基本的.我想构建一个查询以按升序返回对给定值求和的所有行.我不知道我怎么能做到这一点.使用 sum() 只返回一行.我试过一个子查询，但它返回所有行.我不想让任何人做我的工作，我只想让你帮我解决这个问题.有人有想法吗?

My knowledge of MySQL is basic. I want to build a query to return all rows that sum a given value, in ascending order. I can't figure out how I can do that. Using sum() only returns one row. I've tried a subquery but it returns all rows. I don't want anybody do my work, I just want you to help me to figuring this out. Anybody have an idea?

如何检索其提交的值"总和为 30 的所有行示例:

How to retrieve all rows that its filed "value" sum 30 Example:

given value: 30
field to sum: value


table:
id name      value  order
1  name1     3      1
2  name2     10     6
3  name3     13     3
4  name4     5      8
5  name5     20     25

因此，查询必须返回:编号 1、编号 3、编号 2、编号 4

So, the query must return: id 1, id 3, id 2, id 4

提前致谢.

推荐答案

set @total:=0;
select id, name, value, `order`
from 
(select 
 id, name, value, `order`, 
 @total:=if(@total is null, 0, @total)+`order` as total
 from THE_TABLE
 order by `order`
) as derived 
where total<=30;

这篇关于返回总和小于给定值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！