总和直到一定点-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子句以赋予直到"短语任何含义.鉴于此，可以使用来自学生ORDER BY xxx LIMIT N的SELECT SUM(money)获得``前''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中LIMIT子句的另一个嵌套SELECT插入.但是，所有这些气味似乎都相当低效！通常，当嵌套的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屋！