只是想知道如何创建一个临时表，然后从脚本中进一步选择。

例子。

CREATE TEMPORARY TABLE TEMP_TABLE1 AS 
Select 
L.ID,
SUM(L.cost)/2 as Costs,
from Table1 L
JOIN Table2 C on L.ID = C.ID
Where C.name  = 'mike'
Group by L.ID



Select 
Count(L.ID)
from Table1 L
JOIN TEMP_TABLE1 TT1 on L.ID = TT1.ID;
Where L.ID not in (TT1) 
And Sum(L.Cost) > TT1.Costs

理想情况下，我希望有一个临时表，然后在稍后的脚本中使用它作为引用。

任何帮助都会很棒!

最佳答案

您只需将该表称为 temp.<table> 或 <table> 后者仅当它是一个唯一的表名时。

根据:-

If a schema-name is specified, it must be either "main", "temp", or the name of an attached database. In this case the new table is created in the named database. If the "TEMP" or "TEMPORARY" keyword occurs between the "CREATE" and "TABLE" then the new table is created in the temp database. It is an error to specify both a schema-name and the TEMP or TEMPORARY keyword, unless the schema-name is "temp". If no schema name is specified and the TEMP keyword is not present then the table is created in the main database.

SQL As Understood By SQLite - CREATE TABLE

以下示例创建 3 个表:-

1. 具有 3 列的表 1 作为永久表。
2. table1 永久 table1 的临时副本。
3. temp_table 永久表 1 的另一个临时副本。

:-

DROP TABLE IF EXISTS temp.table1;
DROP TABLE IF EXISTS table1;
DROP TABLE IF EXISTS temp_table;
CREATE TABLE table1 (columnA INTEGER,columnB INTEGER, columnC INTEGER);

1. 创建永久表 1 时，它加载了 4 行

:-

INSERT INTO table1 (columnA,columnB,columnC) VALUES 
 (1,5,20),
 (2,7,21),
 (3,8,80),
 (4,3,63);
CREATE TEMP TABLE table1 AS select * from table1;;
CREATE TEMPORARY TABLE temp_table AS SELECT * FROM table1;

1. 然后将两个临时表用于 union all 以基本上复制行，但将源表的指示符作为新列 from_table
   1. 并不是说使用了两种引用临时表的形式。 <强> temp. 和表名。
   2. 后者仅在临时表是唯一表名时可用。

:-

SELECT 'temp_table' AS from_table,* FROM temp_table
UNION ALL 
SELECT 'temp.table1' as from_table,* FROM temp.table1;

结果是:-

重新添加示例:-

CREATE TEMPORARY TABLE TEMP_TABLE1 AS 
Select 
L.ID,
SUM(L.cost)/2 as Costs,
from Table1 L
JOIN Table2 C on L.ID = C.ID
Where C.name  = 'mike'
Group by L.ID



Select 
Count(L.ID)
from Table1 L
JOIN TEMP_TABLE1 TT1 on L.ID = TT1.ID;
Where L.ID not in (TT1) 
And Sum(L.Cost) > TT1.Costs

此示例存在一些问题，禁止滥用聚合(已注释掉)以下工作。

• 注意 为方便起见，我添加了一个 _ 到表名。

:-

DROP TABLE IF EXISTS Table_1;
DROP TABLE IF EXISTS Table_2;
DROP TABLE If EXISTS temp.temp_table1;
CREATE TABLE Table_1 (ID INTEGER PRIMARY KEY, cost REAL);
CREATE TABLE Table_2 (ID INTEGER PRIMARY KEY, name TEXT);
INSERT INTO Table_1 (cost) VALUES (100.45),(56.78),(99.99);
INSERT INTO Table_2 (name) VALUES ('mike'),('mike'),('fred');

CREATE TEMP TABLE temp_table1 AS
SELECT L.ID, 
    sum(L.cost)/2 as Costs
FROM Table_1 L
    JOIN Table_2 C ON L.ID = C.ID
WHERE C.name = 'mike'
GROUP BY L.ID;

SELECT 
    count(L.ID)
FROM Table_1 L
   JOIN temp_table1 TT1 ON  L.ID = TT1.[L.ID]
WHERE 
    L.ID NOT IN (TT1.[L.ID])
    -- AND Sum(L.cost) > TT1.costs --<<<< misuse of aggregate

问题是基于列名 L.ID 所以必须将其括起来(这里的规则 SQL As Understood By SQLite - SQLite Keywords 适用) [ 和 ] 上面已经用过了。

• 当然，您可以通过使用 AS 命名该列来规避对附件的需求。 例如 SELECT L.ID AS lid, --<<<< AS lid ADDED SUM(L.cost)/2 as Costs, ,.......

添加以下内容可能适合避免滥用聚合:-

GROUP BY L.ID
HAVING sum(L.cost) > TT1.costs

将以下内容添加到脚本末尾:-

 SELECT 
    count(L.ID), *
FROM Table_1 L
   JOIN temp_table1 TT1 ON  L.ID = TT1.[L.ID];

结果:-

关于SQLITE，创建一个临时表然后从中选择，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/50093776/