mysql - 要选择的 SQL 查询，直到 SUM(users_count) 达到 1000

Question

我需要一个 sql 查询来从我的消息队列中选择行，直到 SUM(users_count) 达到最多 1000。但是 如果只有一行返回并且该行的 users_count 是大于 1000。

我需要类似的东西:(我添加了自己的关键字)

SELECT * FROM `messages_queue` UNTIL SUM(users_count) < 1000 AT LEAST 1 ROW

这是我的表结构:

messages_queue
- msg_id
- msg_body
- users_count (number of message recieptors)
- time (insert time)

Answer 1

此解决方案将执行累积求和，当总和超过 1000 时停止:

SELECT NULL AS users_count, NULL AS total
  FROM dual
 WHERE (@total := 0)
 UNION
SELECT users_count, @total := @total + users_count AS total
  FROM messages_queue
 WHERE @total < 1000;

这意味着如果你有两个值，比如 800，总和将是 1600。第一个 SELECT 只是初始化 @total 变量。

如果你想防止总和超过 1000，除了单行的值大于 1000 的情况外，我认为这是可行的，尽管你需要对其进行一些严格的测试:

SELECT NULL AS users_count, NULL AS total, NULL AS found
  FROM dual
 WHERE (@total := 0 OR @found := 0)
 UNION
SELECT users_count, @total AS total, @found := 1 AS found
  FROM messages_queue
 WHERE (@total := @total + users_count)
   AND @total < 1000
 UNION
SELECT users_count, users_count AS total, 0 AS found
  FROM messages_queue
 WHERE IF(@found = 0, @found := 1, 0);