SQLITE，创建一个临时表，然后从中选择 [英] SQLITE, Create a temp table then select from it

查看：154 发布时间：2021/8/23 19:26:51 sqlite

本文介绍了SQLITE，创建一个临时表，然后从中选择的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

示例.

创建临时表 TEMP_TABLE1 AS选择身份证，SUM(L.cost)/2 作为成本，来自表 1 LJOIN Table2 C on L.ID = C.ID其中 C.name = 'mike'按 L.ID 分组选择计数(L.ID)来自表 1 L在 L.ID = TT1.ID 上加入 TEMP_TABLE1 TT1；L.ID 不在的地方 (TT1)和 Sum(L.Cost) >TT1.成本

理想情况下，我想要一个临时表，然后在脚本中稍后使用它来引用.

任何帮助都会很棒！

解决方案

您只需将表格称为 temp.

或

后者仅当它是唯一的表名时.

根据:-

<块引用>

如果指定了模式名，它必须是main"、temp"或附加数据库的名称.在这种情况下，新表是在命名数据库中创建.如果TEMP"或TEMPORARY"关键字发生在CREATE"和TABLE"之间，然后创建新表在临时数据库中.同时指定模式名和TEMP 或 TEMPORARY 关键字，除非架构名称是temp".如果没有模式名称已指定且 TEMP 关键字不存在，则表在主数据库中创建.

<小时>

重新添加示例:-

<块引用>

创建临时表 TEMP_TABLE1 AS选择身份证，SUM(L.cost)/2 作为成本，来自表 1 LJOIN Table2 C on L.ID = C.ID其中 C.name = 'mike'按 L.ID 分组选择计数(L.ID)来自表 1 L在 L.ID = TT1.ID 上加入 TEMP_TABLE1 TT1；L.ID 不在的地方 (TT1)和 Sum(L.Cost) >TT1.成本

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

注意，为方便起见，我在表名中添加了 _.

DROP TABLE IF EXISTS Table_1;如果存在表_2，则删除表；删除表如果存在 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');创建临时表 temp_table1 AS选择 L.ID，sum(L.cost)/2 作为成本从表_1 LJOIN Table_2 C ON L.ID = C.IDWHERE C.name = '迈克'按 L.ID 分组；选择计数(L.ID)从表_1 L加入 temp_table1 TT1 ON L.ID = TT1.[L.ID]哪里L.ID 不在 (TT1.[L.ID])-- AND Sum(L.cost) >TT1.costs --<<<<滥用聚合

问题基于列名称 L.ID，因此必须将其括起来(此处的规则

just wondering how i can create a temp table and then select from it further down the script.

Example.

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

Ideally I want to have a temp table then use it later in the script to reference from.

Any help would be great!

解决方案

You simply refer to the table as temp.<table> or <table> the latter only if it is a unique table name.

As per :-

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

The following example creates 3 tables :-

table1 with 3 columns as a permanent table.
table1 a temporary copy of the permanent table1.
temp_table another temporary copy of the permanent table1.

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);

When creating the permanent table 1 it is loaded with 4 rows

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;

both temp tables are then used to in a union all to basically duplicate the rows, but with an indicator of the source table as a new column from_table
1. Not that two forms of referring to the temp tables are used. temp. and just the table name.
2. The latter only usable if the temporary table is a unique table name.

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

The result being :-

Re addition of example :-

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

There are a few issues with this example bar the misuse of the aggregate (commented out) the following works.

Note for my convenience I've added an _ to the table names.

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

The issues are based upon the column name being L.ID so this has to be enclosed (rules here SQL As Understood By SQLite - SQLite Keywords apply) [ and ] have been used above.

of course you could circumvent the need for enclosure by naming the column using AS e..g SELECT L.ID AS lid, --<<<< AS lid ADDED SUM(L.cost)/2 as Costs, ,.......

Adding the following may be suitable for getting around the misuse of aggregate :-

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

Adding the following to the end of the script :-

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

results in :-

这篇关于SQLITE，创建一个临时表，然后从中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

登录关闭

扫码关注1秒登录

发送“验证码”获取 | 15天全站免登陆

SQLITE，创建一个临时表，然后从中选择 [英] SQLITE, Create a temp table then select from it

问题描述

重新添加示例:-

Re addition of example :-

相关文章

数据库最新文章

热门教程

热门工具

登录关闭

SQLITE，创建一个临时表，然后从中选择 [英] SQLITE, Create a temp table then select from it

问题描述

重新添加示例:-

Re addition of example :-

相关文章

数据库最新文章

热门教程

热门工具

登录 关闭

登录关闭