mysql - mysql-选择，直到总和达到一个数字

我需要选择记录，直到一列的总数达到可变数为止。

我有这样一种查询，但不是100％。我还希望看到call_date，并且希望按日期顺序进行计数。

SELECT NULL AS inbound_duration, NULL AS total
  FROM dual
 WHERE (@total := 0)
 UNION
SELECT inbound_duration,  @total := @total + inbound_duration AS total
FROM `records` where calling_user = '1' and call_date LIKE '2016-05-%%' and @total < 5000 ORDER BY call_date

http://sqlfiddle.com/#!9/2e74ff

最佳答案

我尝试了这一点（请注意：您有两行具有相同的call_date：我认为所获得的顺序不能确定，除非您按-ex。inbound_duration或其他字段的顺序指定其他条件）：

# DROP TABLE records;
CREATE TABLE `records` (
  `inbound_duration` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
  `call_date` datetime NOT NULL,
  `calling_user` varchar(25) COLLATE utf8_unicode_ci NOT NULL
);

INSERT INTO records
    (inbound_duration, call_date, calling_user)
VALUES
    (100, '2016-05-05 00:00:00', 1),
    (1000, '2016-05-01 00:00:00', 1),
    (900, '2016-05-03 00:00:00', 1),
    (1500, '2016-05-02 00:00:00', 1),
    (2000, '2016-05-04 00:00:00', 1),
    (2500, '2016-05-05 00:00:00', 1)
;
SELECT * FROM records  ORDER BY call_date;


 SELECT NULL AS call_date, NULL AS inbound_duration, NULL AS total
  FROM dual
 WHERE @total := 0
 UNION ALL
SELECT call_date, inbound_duration,  @total := @total + inbound_duration AS total
 FROM (SELECT * FROM records  ORDER BY call_date) C where calling_user = '1' and call_date LIKE '2016-05-%%' and @total < 5000

;

DROP TABLE records;

输出：

    inbound_duration    call_date   calling_user
1   1000    01.05.2016 00:00:00 1
2   1500    02.05.2016 00:00:00 1
3   900 03.05.2016 00:00:00 1
4   2000    04.05.2016 00:00:00 1
5   100 05.05.2016 00:00:00 1
6   2500    05.05.2016 00:00:00 1

    call_date   inbound_duration    total
1   01.05.2016 00:00:00 1000    1000
2   02.05.2016 00:00:00 1500    2500
3   03.05.2016 00:00:00 900 3400
4   04.05.2016 00:00:00 2000    5400

关于mysql - mysql-选择，直到总和达到一个数字，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42972498/