代码之家 › 专栏 › 技术社区 › HSchmale

相关子查询中没有此类列

correlated-subquery sqlite sql

0

HSchmale · 技术社区 · 7 年前

所以我写了一个与 this answer 是的。但是我得到了一个错误声明:没有这样的专栏 b.when 是的。

我的选择语句:

SELECT
    mileage,
    (SELECT b.mileage FROM MileageEvents as b WHERE `b.when` < `a.when` ORDER BY `b.when` DESC LIMIT 1) as last_mileage,
    gallons,
    cost_per_gallon,
    `when`
FROM MileageEvents as a

我知道我以前写过这样的查询,但是我似乎不知道这个查询是怎么回事。我做错什么了?

一堆我的数据库:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "MileageEvents" (
    "mileage"   INTEGER,
    "when"  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    "cost_per_gallon"   INTEGER,
    "gallons"   INTEGER,
    "id"    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "car_id"    INTEGER NOT NULL
);
INSERT INTO MileageEvents VALUES(150000,'2019-03-12',3.3500000000000000888,12,1,1);
INSERT INTO MileageEvents VALUES(150300,'2019-03-19',3.25,12,2,1);
INSERT INTO MileageEvents VALUES(150693,'2019-03-22',3.4500000000000001776,12,3,1);
INSERT INTO MileageEvents VALUES(151000,'2019-03-25',3.3900000000000001243,12,4,1);
INSERT INTO MileageEvents VALUES(151600,'2019-04-01',2.25,12,5,1);
INSERT INTO MileageEvents VALUES(151883,'2019-06-10 23:01:43',2.4500000000000001776,11.695999999999999729,6,1);
DELETE FROM sqlite_sequence;
INSERT INTO sqlite_sequence VALUES('MileageEvents',6);
COMMIT;

1 回复 | 直到 7 年前

1

2

Shawn 7 年前

(首先,我将使用普通的双引号转义,而不是mysql样式的反勾号,或者更好的是,使用不是保留关键字的标识符)

你需要使用 b."when" 是的。你所做的是处理整个字符串 b.when 作为标识符,表名和列名之间没有分隔符。

重写:

SELECT
    mileage,
    (SELECT b.mileage FROM MileageEvents as b WHERE b."when" < a."when" ORDER BY b."when" DESC LIMIT 1) as last_mileage,
    gallons,
    cost_per_gallon,
    "when"
FROM MileageEvents as a;

注意:如果使用sqlite 3.25或更新版本,则可以完全避免子查询:

SELECT
    mileage,
    lag(mileage) OVER (ORDER BY "when") AS last_mileage,
    gallons,
    cost_per_gallon,
    "when"
FROM MileageEvents;