达到SUM时停止查询(mysql) [英] Stop query when SUM is reached (mysql)

问题描述

我有一个我正在研究的数据库.我正在寻找的是与查询中SUM(#)达到6的行相关的日期.我现在的查询将给出日期，当列中的数字为6时，而不是前几行的总和.下面的例子

I have a database with colums I am working on. What I am looking for is the date associated with the row where the SUM(#) reaches 6 in a query. The query I have now will give the date when the number in the colum is six but not the sum of the previous rows. example below

Date     number
----     ------
6mar16    1
8mar16    4
10mar16   6
12mar16   2

我想查询一个获取10mar16日期的查询，因为在该日期该数字现在大于6.早期的日期总计不超过6个.

I would like to get a query to get the 10mar16 date because on that date the number is now greater than 6. Earlier dates wont total up to six.

以下是我一直在处理的查询示例:

Here is an example of a query i have been working on:

SELECT max(date) FROM `numbers` WHERE `number` > 60 

推荐答案

您可以使用此查询，该查询跟踪累积的总和，然后返回满足条件的第一个:

You could use this query, which tracks the accumulated sum and then returns the first one that meets the condition:

select   date
from     (select * from mytable order by date) as base,
         (select @sum := 0) init
where    (@sum := @sum + number) >= 6
limit 1

SQL提琴

这篇关于达到SUM时停止查询(mysql)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！