python - SQlite:保存/注册子查询以供重复使用

Question

假设我有一家商店，其产品成本很少变化，因此我设置了一个成本表，如下所示:

CREATE TABLE costs VALUES (
    itemid INTEGER
    cost DOUBLE
    date DATETIME
    FOREIGN KEY(itemid) REFERENCES items(itemid)
);

如果我想要当前成本和上次更新时间的输出，我可以这样做:

SELECT items.name, cost, latestdate
FROM items
LEFT NATURAL JOIN (
    SELECT itemid, costs.cost, latestdate
    FROM (
        SELECT itemid, MAX(date) as latestdate
        FROM costs
        GROUP BY itemid
    )
    LEFT NATURAL JOIN costs
    WHERE date = latestdate
 );

是否可以保存子查询，以便该查询可以写成如下形式:

SELECT items.name, cost, latestdate
FROM items
LEFT NATURAL JOIN get_current_cost();

或者，在“提前成本”情况下:

SELECT items.name, cost
FROM items
LEFT NATURAL JOIN (
    SELECT itemid, costs.cost, latestdate
    FROM (
        SELECT itemid, MAX(date) as latestdate
        FROM costs
        GROUP BY itemid
        WHERE date < *somedate*
    )
    LEFT NATURAL JOIN costs
    WHERE date = latestdate
 );

-- Change To

SELECT items.name, cost
FROM items
LEFT NATURAL JOIN get_cost_by(*somedate*)

Answer 1

那个东西叫做view :

CREATE VIEW current_cost AS
SELECT itemid, cost, MAX(date) AS latestdate
FROM costs
GROUP BY itemid;

(在 SQLite 中， a bare column in an aggregate query works 。)

但是，无法使用参数。