sql - 在 View 中访问子查询中的值

Question

例如，我设置了一个简单的 SQLite 模式，如下所示:

sqlite> CREATE TABLE foo (a TEXT, b INT, PRIMARY KEY(a, b));
sqlite> INSERT INTO foo (a, b) VALUES ('x', 5);
sqlite> INSERT INTO foo (a, b) VALUES ('x', 10);
sqlite> INSERT INTO foo (a, b) VALUES ('x', 15);
sqlite> INSERT INTO foo (a, b) VALUES ('y', 5);
sqlite> INSERT INTO foo (a, b) VALUES ('y', 12);
sqlite> INSERT INTO foo (a, b) VALUES ('y', 16);

b表示时间戳和 a标识一个对象。当然，实际应用程序包含更多列。

我想创建一个包含 (a, b) 映射的 View 到 b 的下一个较小值 foo 中存在条目.对于上面的示例，它应该如下所示:

a  | b   | previous_b
---------------------
x  | 10  | 5
x  | 15  | 10
y  | 12  | 5
y  | 16  | 12

我试图创建一个这样的 View :

CREATE VIEW prev
AS
    SELECT
        a,
        b AS current_b,
        (SELECT MAX(b) FROM foo WHERE b < current_b) AS previous_b
FROM foo;

但是，我似乎无法访问 current_b在子查询中:

sqlite> SELECT * FROM prev;
Error: no such column: current_b

我怎么解决这个问题？如果这是不可能的，还有其他选择吗？

Answer 1

你不能使用这样的别名。采用

CREATE VIEW prev
AS
    SELECT
        f1.a,
        f1.b AS current_b,
        (SELECT MAX(f2.b) FROM foo f2 WHERE f2.b < f1.b) AS previous_b
    FROM foo as f1;