字段总和小于N的SQL选择元素

Question

鉴于我有一个包含以下非常简单的内容的表：

# select * from messages;
  id | verbosity 
 ----+-----------
   1 |        20
   2 |        20
   3 |        20
   4 |        30
   5 |       100
 (5 rows)

我想选择N条消息，其详细程度的总和低于Y（出于测试目的，假设它应该为70，那么正确的结果将是ID为1,2,3的消息）。对我来说真的很重要，该解决方案应该独立于数据库（它至少应在Postgres和SQLite上运行）。

我正在尝试类似的东西：

SELECT * FROM messages GROUP BY id HAVING SUM(verbosity) < 70;

但是，它似乎没有按预期方式工作，因为它实际上并未汇总详细列中的所有值。

对于任何提示/帮助，我将不胜感激。

Answer 1

SELECT m.id, sum(m1.verbosity) AS total
FROM   messages m
JOIN   messages m1 ON m1.id <= m.id
WHERE  m.verbosity < 70    -- optional, to avoid pointless evaluation
GROUP  BY m.id
HAVING SUM(m1.verbosity) < 70
ORDER  BY total DESC
LIMIT  1;

这假定id您的示例中有一个独特的，升序的。

在现代Postgres中-或通常在 现代标准SQL中使用 （但在SQLite中 不是 ）：

简单的CTE

WITH cte AS (
   SELECT *, sum(verbosity) OVER (ORDER BY id) AS total
   FROM   messages
   )
SELECT *
FROM   cte
WHERE  total <= 70
ORDER  BY id;

递归CTE

对于只检索少量集合的大表，应该更快。

WITH RECURSIVE cte AS (
   (  -- parentheses required
   SELECT id, verbosity, verbosity AS total
   FROM   messages
   ORDER  BY id
   LIMIT  1
   )

   UNION ALL 
   SELECT c1.id, c1.verbosity, c.total + c1.verbosity 
   FROM   cte c
   JOIN   LATERAL (
      SELECT *
      FROM   messages
      WHERE  id > c.id
      ORDER  BY id
      LIMIT  1
      ) c1 ON  c1.verbosity <= 70 - c.total
   WHERE c.total <= 70
   )
SELECT *
FROM   cte
ORDER  BY id;

除以外的所有标准功能 LIMIT 。

严格来说，没有“数据库无关”之类的东西。有各种SQL标准，但没有RDBMS完全符合。LIMIT适用于PostgreSQL和SQLite（和其他一些）。使用TOP 1的SQL
Server，rownum用于Oracle。这是Wikipedia上的完整列表。

在SQL：2008标准是：

...
FETCH  FIRST 1 ROWS ONLY

… PostgreSQL支持-几乎没有其他RDBMS。

适用于更多系统的纯替代方法是将其包装在子查询中，然后

SELECT max(total) FROM <subquery>

但这是缓慢而笨拙的。

SQL提琴。