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 temp.table1;
DROP TABLE IF EXISTS table1;
DROP TABLE IF EXISTS temp_table;
CREATE TABLE table1 (columnA INTEGER,columnB INTEGER, columnC INTEGER);
创建永久表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;
然后使用两个临时表进行合并以基本上复制行，但是将源表的指示符作为新列from_table
并非使用两种形式的临时表引用。temp.还有表名。
仅当临时表是唯一的表名时，后者才可用。
：-
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 --<<<
京公网安备 11010802041100号