SQLite 说“没有这样的列:rowid"；使用子查询时 [英] SQLite says "no such column: rowid" when using sub-queries

问题描述

这是一个工作正常的查询:

Here's a query that works fine:

SELECT rowid as msg_rowid, a, b, c FROM messages m1

还有一个也不错:

SELECT rowid as match_rowid FROM messages m2 WHERE x LIKE '%abc%'

但是如果我把它们放在一起，SQLite 会抱怨:

But if I put them together as follows, SQLite complains:

SELECT rowid as msg_rowid, a, b, c FROM messages m1
JOIN
    (SELECT rowid as match_rowid FROM messages m2 WHERE x LIKE '%abc%')
ON
    msg_rowid >= match_rowid - 10 AND msg_rowid <= match_rowid + 5

给出看似误导性的错误消息:没有这样的列:rowid.

giving what looks like a misleading error message: No such column: rowid.

我该如何解决这个问题?

How can I fix this?

如果我使用 messages.timestamp 字段而不是 rowid，则查询运行良好:

The query runs fine if I use a messages.timestamp field instead of rowid:

SELECT timestamp as msg_ts, a, b, c FROM messages m1
JOIN
    (SELECT timestamp as match_ts FROM messages m2 WHERE x LIKE '%abc%')
ON
    msg_ts >= match_ts - 10 AND msg_ts <= match_ts + 5

这是一个错误，还是使用 rowid 的设计限制?

Is this a bug, or a by-design restriction on the use of rowid?

推荐答案

由 GuZzie 在评论中回答.出于某种原因，SQLite 要求我明确说明外部 rowid:

Answered by GuZzie in a comment. For some reason SQLite requires me to be explicit about the outer rowid:

SELECT m1.rowid as msg_rowid, a, b, c FROM messages m1
       ^^ HERE
JOIN
    (SELECT rowid as match_rowid FROM messages m2 WHERE x LIKE '%abc%')
ON
    msg_rowid >= match_rowid - 10 AND msg_rowid <= match_rowid + 5

这篇关于SQLite 说“没有这样的列:rowid"；使用子查询时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！