通过行值的总和限制SQL [英] Limit SQL by the sum of the row's value

问题描述

所以我有点困惑，我有一个这样的表设置

So I'm kind of stumped here, I have a table setup like this

+-----------+------+
| Timestamp | Size |
+-----------+------+
|   1-1-13  + 10.3 +
+-----------+------+
|   1-3-13  +  6.7 +
+-----------+------+
|   1-5-13  +  3.0 +
+-----------+------+
|   1-9-13  + 11.4 +
+-----------+------+

我想知道是否有任何方式可以运行这样的查询

And I'm wondering if there's any way to run a query like this

SELECT * FROM table ORDER BY timestamp ASC LIMIT BY (SUM(size) <= 20.0);

这应该抓住前三行，因为前三行的in之和为20.但是，它不一定总是等于20的三行.有时第一行的值可能为20，在那种情况下，它只能抓住第一个.

This should grab the first three rows, because the sum of the size in of the first 3 rows is 20. However, it might not always be 3 rows that equal 20. Sometimes the first row might have a value of 20, and in that case, it should only grab the first one.

我已经知道，可以在运行查询后在PHP中快速计算总和，但是我正在尝试仅使用MySQL来实现.

I'm already aware that this it's possible to quickly calculate the sum in PHP after the query is run, but I'm trying to accomplish this with just MySQL.

推荐答案

您要添加运行总计，并基于此限制，以下方法应该起作用:

You want to add a running total, and limit based on that, the following should work:

SET @runtot:=0;
 SELECT 
    q1.t,
    q1.s,
    (@runtot := @runtot + q1.s) AS rt
 FROM 
    (SELECT Date AS t,
     SIZE AS s
     FROM  Table1
     ORDER  BY Date
     ) AS q1
WHERE @runtot + q1.s <= 20

此处演示- SQL小提琴

这篇关于通过行值的总和限制SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！