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

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;

但是它似乎没有按预期工作，因为它实际上并没有对 verbosity 列的所有值求和。

如果有任何提示/帮助，我将不胜感激。

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;

所有标准 SQL，LIMIT 除外。

严格来说，没有所谓的“数据库无关”。有各种 SQL 标准，但没有完全符合的 RDBMS。 LIMIT 适用于 PostgreSQL 和 SQLite(以及其他一些)。对 SQL Server 使用 TOP 1，对 Oracle 使用 rownum。这是一个 comprehensive list on Wikipedia.

SQL:2008 standard会是:

...
FETCH  FIRST 1 ROWS ONLY

... PostgreSQL 支持 - 但几乎没有任何其他 RDBMS。

适用于更多系统的纯粹替代方案是将其包装在子查询中

SELECT max(total) FROM <subquery>

但那是缓慢且笨重的。

db<> fiddle here
<子>旧sqlfiddle