英文:
Using Global Temporary Table with CTE in Firebird SQL
问题
我正在尝试在Firebird SQL中使用带有CTE的全局临时表，但我不确定如何操作。
下面是我尝试实现的示例代码：
CREATE GLOBAL TEMPORARY TABLE temp_table ( Y INTEGER, K INTEGER );
WITH J AS (SELECT Y , K FROM X),
INSERT INTO temp_table
T AS (SELECT Y , K FROM Z INNER JOIN J ON Z.Y=J.Y),
P AS (SELECT R , K FROM U INNER JOIN T ON U.K=T.K)
SELECT R , K , Y FROM P LEFT JOIN temp_table ON P.K=temp_table.K
我的目标是将CTE中的值存储在临时表中，并使用左连接访问这些值。在我的具体情况下，直接在之前的CTE中进行左连接会使运行非常慢，因为涉及到的一个表非常大。我认为使用临时表可能会解决这个问题。
英文:
I am trying to use a Global Temporary Table with CTE in Firebird SQL, but I'm not sure how.
Below is sample code of what I am trying to accomplish
CREATE GLOBAL TEMPORARY TABLE temp_table ( Y INTEGER, K INTEGER );
WITH J AS (SELECT Y , K FROM X),
INSERT INTO temp_table
T AS (SELECT Y , K FROM Z INNER JOIN J ON Z.Y=J.Y),
P AS (SELECT R , K FROM U INNER JOIN T ON U.K=T.K)
SELECT R , K , Y FROM P LEFT JOIN temp_table ON P.K=temp_table.K
My objective is to store values from a CTE in a temporary table and access those values a with a left join. In my specific situation doing a left join directly in a previous CTE will make the run very, very slow because one of the tables involved is a big one. I thought maybe making a temporary table will go around this.
答案1
得分: 1
在Firebird SQL中，你不能直接在同一语句中在CTE（Common Table Expression）中使用全局临时表（GTT）。但是，你可以通过使用临时表和多个SQL语句的组合来实现你的目标。
以下是如何修改你的代码以实现所需结果的示例：
-- 创建一个临时表来存储CTE的结果
CREATE GLOBAL TEMPORARY TABLE temp_table (Y INTEGER, K INTEGER);
-- 将CTE的结果插入临时表
INSERT INTO temp_table (Y, K)
SELECT Y, K
FROM (
-- 定义CTE
WITH J AS (SELECT Y, K FROM X),
T AS (SELECT Y, K FROM Z INNER JOIN J ON Z.Y = J.Y),
P AS (SELECT R, K FROM U INNER JOIN T ON U.K = T.K)
SELECT R, K, Y
FROM P
) CTE_results;
-- 在单独的SELECT语句中使用临时表进行LEFT JOIN
SELECT R, K, Y
FROM P
LEFT JOIN temp_table ON P.K = temp_table.K;
在这段代码中，我们首先创建了临时表temp_table，用于存储CTE的结果。然后，我们使用单独的INSERT INTO语句将CTE的结果插入临时表中。最后，我们可以在单独的SELECT语句中使用临时表进行LEFT JOIN以访问存储的值。
请注意，临时表仅在相同的数据库连接会话中可访问。它将在会话结束或事务提交或回滚时自动删除。
英文:
In Firebird SQL, you cannot directly use a Global Temporary Table (GTT) within a CTE (Common Table Expression) in the same statement. However, you can achieve your objective by using a combination of temporary tables and multiple SQL statements.
Here's an example of how you can modify your code to achieve the desired result:
-- Create a temporary table to store the CTE results
CREATE GLOBAL TEMPORARY TABLE temp_table (Y INTEGER, K INTEGER);
-- Insert the CTE results into the temporary table
INSERT INTO temp_table (Y, K)
SELECT Y, K
FROM (
-- Define the CTE
WITH J AS (SELECT Y, K FROM X),
T AS (SELECT Y, K FROM Z INNER JOIN J ON Z.Y = J.Y),
P AS (SELECT R, K FROM U INNER JOIN T ON U.K = T.K)
SELECT R, K, Y
FROM P
) CTE_results;
-- Use the temporary table in a separate SELECT statement with a LEFT JOIN
SELECT R, K, Y
FROM P
LEFT JOIN temp_table ON P.K = temp_table.K;
In this code, we first create the temporary table temp_table to store the results of the CTE. Then, we insert the CTE results into the temporary table using a separate INSERT INTO statement. Finally, we can use the temporary table in a separate SELECT statement with a LEFT JOIN to access the stored values.
Keep in mind that the temporary table will only be accessible within the same database connection session. It will be automatically dropped when the session ends or when the transaction is committed or rolled back.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论