选择直到SUM(users_count)达到1000的SQL查询 [英] A SQL query to select until SUM(users_count) reaches 1000

问题描述

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

I need a sql query to select rows from my messages queue, until SUM(users_count) reaches at most 1000. BUT there is no problem if there be only one row returned and that row's users_count is greater than 1000.

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

I need something like: (i added my own keywords)

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

这是我的表结构:

messages_queue
-msg_id
-msg_body
-users_count(消息接收器的数量)
-时间(插入时间)

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

推荐答案

此解决方案将执行累计和，并在超过1000时停止:

This solution will perform a cumulative sum, stopping when the sum exceeds 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变量.

That means that if you have two values of, say, 800, the sum total will be 1600. The first SELECT is just to initialise the @total variable.

如果要防止总和超过1000(单行的值大于1000的情况除外)，那么我认为这是可行的，尽管您需要对其进行严格的测试:

If you want to prevent the sum from exceeding 1000, apart from in cases where a single row has a value of greater than 1000, then I think this works, although you'll need to subject it to some rigorous testing:

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);

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