时间戳

首页 > 解决方案 > 可以像临时“表”一样分配查询结果吗？

sqlite - 可以像临时“表”一样分配查询结果吗？

问题描述

我想对查询结果执行一些进一步的数据处理 - 我的查询是：

SELECT id, gamenumber, team, name, points 
FROM games
WHERE gamenumber IN (
    SELECT gamenumber
    FROM games
    WHERE
        (name = 'Tom Smith' AND team = 'team tower') OR
        (name = 'Andy Cooper' AND team = 'team windy')
    GROUP BY gamenumber
    HAVING MIN(name) <> MAX(name)
);

这产生了这个结果：

gamenumber  team    name    points
1000    team tower  Tom Smith   100
1000    team vegas  John Little 90
1000    team windy  Andy Cooper 20
1003    team tower  Tom Smith   89
1003    team bars   Jonathan Swift  21
1003    team hammock    Bill Mccain 31
1003    team windy  Andy Cooper -1

但我需要更深入地研究这一点，然后根据这里的结果总结价值。

所以我在想是否可以在 SQL 中给查询结果一个“名称”？所以我可以做这样的事情（将查询结果命名为 TEMP_TABLE ）：

TEMP_TABLE = SELECT id, gamenumber, team, name, points 
FROM games
WHERE gamenumber IN (
    SELECT gamenumber
    FROM games
    WHERE
        (name = 'Tom Smith' AND team = 'team tower') OR
        (name = 'Andy Cooper' AND team = 'team windy')
    GROUP BY gamenumber
    HAVING MIN(name) <> MAX(name)
);

然后我可以做这样的事情：

SELECT * FROM TEMP_TABLE

这样的事情可能吗？

标签： sqlite

解决方案

你有两种不同的选择。您选择哪一种取决于您需要数据的新鲜程度。

临时表与被查询的原始数据集隔离开来。如果原始数据发生变化，临时表不受影响...

CREATE TEMP TABLE temp_table AS
SELECT id, gamenumber, team, name, points 
FROM games
WHERE gamenumber IN (
    SELECT gamenumber
    FROM games
    WHERE
        (name = 'Tom Smith' AND team = 'team tower') OR
        (name = 'Andy Cooper' AND team = 'team windy')
    GROUP BY gamenumber
    HAVING MIN(name) <> MAX(name)
);

但是，只要原始数据集更新，临时视图就会更新...

CREATE TEMP VIEW temp_view AS
SELECT id, gamenumber, team, name, points 
FROM games
WHERE gamenumber IN (
    SELECT gamenumber
    FROM games
    WHERE
        (name = 'Tom Smith' AND team = 'team tower') OR
        (name = 'Andy Cooper' AND team = 'team windy')
    GROUP BY gamenumber
    HAVING MIN(name) <> MAX(name)
);

请记住，如果临时表或视图名称与现有的永久表或视图匹配，它将从那里提取数据！为避免这种情况，请在表名称前加上temp.如下所示...

SELECT * FROM temp.temp_table;
SELECT * FROM temp.temp_view;

推荐阅读