在MySql中多次访问临时表 [英] Accessing a temporary table multiple times in MySql

问题描述

我试图使用一个临时表作为SELECT语句的中间结果持有人.问题是尽管我无法在其他查询语句中多次访问临时表，但我希望这是可能的，即使临时表无用.

I have tried to use a temporary table as an intermediate result holder for a SELECT statement. The problem is though that I can't access the temp table multiple times in other queries statement which I hoped would be possible i.e. makes the temp table useless.

在MySql中是否有替代临时表的方法，允许我提取SQL语句.

Is there an alternative to temporary tables in MySql that allows me to extract my SQL statement.

我无法使用存储过程(无法从公司使用的Web框架版本访问它们)，而且我也不想使用游标.

I can't use store procedures (can't access them from the web-framework version used in the company) and I don't want to use a cursor.

我的代码看起来像这样:

Well my code looks somewhat like this:

创建临时表:

CREATE TEMPORARY TABLE dates_with_entries (
  seq  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  datum VARCHAR(32)
);

INSERT INTO dates_with_entries (datum) SELECT datum AS Datum from project_times
    WHERE user_id = 20 GROUP BY datum ORDER BY datum desc LIMIT 13;

然后，我使用临时表的代码看起来像这样(我将其简化为我遇到的问题.)

Then the code where I use my temp table looks somewhat like this (I simpled it down to the problem I have..)

SELECT 
...
FROM (SELECT entrie_date AS datum FROM dates_with_entries ) AS sub_result
INNER JOIN project_times
    ON sub_result.datum = project_times.datum AND project_times.user_id = 20
LEFT JOIN works AS w ON project_times.work_id = w.id
LEFT JOIN sub_projects AS sp ON sp.id = w.sub_project_id
LEFT JOIN projects AS p ON p.id = sp.project_id
GROUP BY datum
UNION(
    SELECT
      ..
  FROM (SELECT entrie_date AS datum FROM dates_with_entries ) AS sub_result
  INNER JOIN project_times AS pt ON pt.datum = sub_result.datum
  INNER JOIN works AS w on w.id = pt.work_id
  INNER JOIN sub_projects AS sp on w.sub_project_id = sp.id
  INNER JOIN projects AS p ON sp.project_id = p.id
  WHERE pt.user_id = 20
);

以后将用ruby替换数字，这仅用于测试SQL语句.

The numbers will be later on replaced by ruby, this is only for testing the SQL statement.

推荐答案

一种解决方法是简单地创建一个真实"表，而不是一个临时表.

One way around this is to simply create a "real" table, rather than a temporary table.

临时表提供的优点:

1. 名称间隔.您可以创建多个 具有相同名称的临时表 在单独的会话中.
2. 自动清除.完成后，您无需显式删除表 它.当您结束会话时，它消失了

1. Namespacing. You can create multiple temporary tables with the same name in separate sessions.
2. Automatic cleanup. You don't need explicitly drop the table when you're done with it. It goes away when you end your session

如果#1对您至关重要，那么您可能需要坚持使用临时表.否则，如果一次仅运行该程序的一个实例，或者如果您动态创建表名以使其唯一，则建议您为此表选择一个适当的唯一名称并将其创建为真实"表，然后完成后放下它.

If #1 is crucial for you, then you probably need to stick with temporary tables. Otherwise, if only one instance of this program runs at a time, or if you create the table name dynamically to make it unique, I recommend that you choose an appropriately unique name for this table and create it as a "real" table, then drop it when you're done.

这篇关于在MySql中多次访问临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！