总和直到某个点 - MySql [英] Sum until certain point - MySql

问题描述

如何查询和显示记录直到达到一定数量?

How to do query and display the records until it reaches a certain number?

假设你要选择学生，直到学生的钱总和达到1000?

Suppose you want to select student until the total sum of the student's money reaches 1000?

 Student ID   Student Name   Student Money 
 ---------    -----------     --------------
   1           John            190
   2           Jenny           290
   3           Ben             200
   4           Andy            120
   5           Lynna           300

如果我想停在 500，我会得到记录号 1 和 2 (190 + 290).如果我想停在 1000，我会得到记录 1 到 4.

If I wanna stop at 500, I would get record number 1 and 2 (190 + 290). If I wanna stop at 1000, I would get record 1 until 4.

推荐答案

SQL 表没有内在"顺序，因此您必须指定一些 ORDER BY 子句以赋予直到"短语任何含义.鉴于此，可以使用 SELECT SUM(money) FROM student ORDER BY xxx LIMIT N 获得第一"N 条记录的总和.使用具有自然顺序整数的辅助表 INTS，您可以找到最合适的N 类似于:

There is no "intrinsic" order to a SQL table, so you'll have to specify some ORDER BY clause to give that "until" phrase any meaning. Given that, the sum of the ``first'' N records can be obtained with a SELECT SUM(money) FROM student ORDER BY xxx LIMIT N. Using an auxiliary table INTS which has integers in natural order, you can find the maximum suitable N by something like:

SELECT MAX(N) FROM INTS
WHERE (SELECT SUM(money) FROM student ORDER BY xxx LIMIT N) < 1000

最后将它作为另一个嵌套 SELECT 插入到整个 SELECT 中的 LIMIT 子句中.不过，所有这些闻起来都会相当低效！通常，当嵌套的 SELECT 看起来太多且太慢时，另一种方法是逐步执行此操作:首先使用累进总和"构建一个临时表，然后使用它来帮助您找到所需的限制.

and finally insert this as another nested SELECT for the LIMIT clause in your overall SELECT. All of this smells like it would be rather inefficient, though! As often when nested SELECTs seem too many and too slow, an alternative is doing this in steps: first build a temporary table with the "progressive sums", then use that to help you find the limit you need.

这篇关于总和直到某个点 - MySql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！