sql - 如何使用来自周围查询的列作为 SQLite 中 LIMIT 的值？

Question

在 SQLite 中，可以使用任何返回或可以转换为整数的表达式作为 LIMIT 的参数。 .

Any scalar expression may be used in the LIMIT clause, so long as it evaluates to an integer or a value that can be losslessly converted to an integer. [...]

这意味着，以下是可能的(我将在下面进一步展示用于此的数据库):

SELECT * FROM event_attendees
WHERE dtime IS NULL
ORDER BY rtime ASC
LIMIT (
  SELECT max_participants FROM `events` WHERE id = 1
);

此查询将运行并返回结果。

但是，我试图在子查询中使用它，但这给了我一条错误消息。

SELECT *
  FROM `events` e
       JOIN
       event_attendees ON `events`.id = event_attendees.event_id AND 
            event_attendees.id IN (
                SELECT id
                    FROM event_attendees ea2
                    WHERE ea2.dtime IS NULL AND 
                        ea2.event_id = e.id
                    ORDER BY rtime ASC
                    LIMIT e.max_participants
             );

在 SQLFiddle错误信息如下。

could not prepare statement (1 no such column: e.max_participants)

当我使用 SQLiteStudio 3 运行它时，错误消息是这样的:

Error while executing SQL query on database 'foo': no such column events.max_participants

我正在运行的架构如下所示:

CREATE TABLE `events` (
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    name             VARCHAR,
    max_participants INT
);

CREATE TABLE event_attendees (
    id          INTEGER  PRIMARY KEY AUTOINCREMENT,
    event_id    INTEGER  REFERENCES events (id),
    attendee_id INTEGER, /* we don't need attendees for this example */
    rtime       DATETIME NOT NULL,
    dtime       DATETIME
);

我使用以下示例值。

INSERT INTO `events` ( id, name,  max_participants) 
VALUES               ( 1,  'foo', 3 ), 
                     ( 2,  'bar', 4 );

INSERT INTO `event_attendees` 
(id, event_id, attendee_id, rtime,                 dtime) VALUES
(1,  1,        1,           '2017-08-02 08:08:00', NULL                 ),
(2,  1,        2,           '2017-08-02 08:09:00', NULL                 ),
(3,  1,        3,           '2017-08-02 08:10:00', NULL                 ),
(4,  1,        4,           '2017-08-02 08:12:00', NULL                 ),
(5,  2,        1,           '2017-08-03 08:08:00', NULL                 ),
(6,  2,        2,           '2017-08-03 08:09:00', '2017-08-04 10:08:00'),
(7,  2,        3,           '2017-08-03 08:10:00', NULL                 ),
(8,  2,        4,           '2017-08-03 08:11:00', NULL                 ),
(9,  2,        5,           '2017-08-03 08:12:00', NULL                 ),
(10, 2,        6,           '2017-08-03 08:13:00', NULL                 ),
(11, 2,        7,           '2017-08-03 08:14:00', NULL                 ),
(12, 2,        8,           '2017-08-03 08:15:00', NULL                 );

我也准备了 an SQLFiddle with this exact data and both these queries .

这些表格描述了参加事件的人员的列表。与 attendee_id 相关的用户表与问题无关，所以我把它排除在外。

我正在尝试选择首先注册该事件的人( rtime )，没有取消( dtime )，受 max_participants 限制事件的参加者上限。我们应该看到的结果是带有这些 event_attendees.id 的行。 s。

id event_id attendee_id
1  1        1
2  1        2
3  1        3
5  2        1
7  2        3
8  2        4
9  2        5

似乎 SQLite 无法看到 e.max_participants子查询中外部查询的列。

我是否遗漏了什么，或者由于我不知道的一些限制，这是不可能的？还有另一种方法吗？

Answer 1

SQLite 确实 describe限制表达式的规则:

Any scalar expression may be used in the LIMIT clause

但是， scalar expression文档中没有定义。我认为对它的更好描述是“标量常数”。
如果您将第一个查询更改为:

SELECT ea.*
FROM event_attendees ea
WHERE dtime IS NULL
ORDER BY rtime ASC
LIMIT (SELECT e.max_participants FROM `events` e WHERE e.id = ea.event_id);

然后你就会明白 SQLite 解析器的问题了。 LIMIT需要是一个常数，但它会从一行更改为下一行。
SQLite 似乎将此扩展到禁止相关子查询。我不完全了解 SQLite 的内部结构。我可以想象 LIMIT子句在 SELECT 之前被解析和执行查询被解析并执行。因此，它不能使用来自外部查询的表。这是猜测，但它可以解释正在发生的事情。
不幸的是，SQLite 不支持窗口函数或类似的功能。所以，做你想做的事是相当复杂且计算量大的。您最好在应用程序层执行此操作。