在用UNION创建临时表时遇到问题 [英] Having issues creating a temporary table out of a UNION

问题描述

我有一个UNION语句，它本身可以很好地执行:

I have a UNION statement that executes just fine by itself:

SELECT "1999999999" AS MobileNo, "Test" AS FirstName, "Last" AS LastName, "268" AS TemplateID, "" AS MISC1, "" AS MISC2 UNION SELECT cust.cellp AS MobileNo, acct.firstname AS FirstName, acct.lastname AS LastName, "268" AS TemplateID, "" AS MISC1, "" AS MISC2 FROM acct INNER JOIN cust ON (cust.socsec=acct.socsec)

但是，当我尝试用CREATE TEMPORARY TABLE包装它时:

However when I try to wrap it with a CREATE TEMPORARY TABLE:

CREATE TEMPORARY TABLE temptable (SELECT "1999999999" AS MobileNo, "Test" AS FirstName, "Last" AS LastName, "268" AS TemplateID, "" AS MISC1, "" AS MISC2 UNION SELECT cust.cellp AS MobileNo, acct.firstname AS FirstName, acct.lastname AS LastName, "268" AS TemplateID, "" AS MISC1, "" AS MISC2 FROM acct INNER JOIN cust ON (cust.socsec=acct.socsec))

我收到此错误:

错误1064(42000):您的SQL语法有错误；在第1行的"UNION SELECT cust.cellp AS MobileNo，acct.firstname AS FirstName，ac"附近检查与您的MySQL服务器版本相对应的手册以使用正确的语法

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT cust.cellp AS MobileNo, acct.firstname AS FirstName, ac' at line 1

任何SELECT语句都可以在其周围的CREATE TEMPORARY TABLE上正常工作，只是UNION不起作用.返回的MySQL错误含糊不清，因此我无法弄清楚问题出在哪里.我尝试将每条SELECT语句括在括号中，但也不满意.

Either SELECT statement works fine with the CREATE TEMPORARY TABLE around it, it is just the UNION that doesn't work. The MySQL error returned is vague so I can't quite figure out what the issue is. I tried wrapping each SELECT statement in parenthesis but it didn't like that either.

我知道，从理论上讲，我可以只使用一个SELECT创建临时表，然后将第二个SELECT的数据添加到表中，但是这种解决方案在这种情况下实际上并不可行，因为它是我正在使用的报告生成器关于只允许输入一个MySQL语句这是非常严格的，所以除非我想重新设计整个系统(我不这样做，管理层也不想让我现在花时间在上面)，否则我必须找到一种方法来做到这一点在单个MySQL语句中工作.

I know that in theory I could just create the temporary table using one SELECT and then add the data from the second one to the table, but that solution is not really feasible in this case as it is a report generator I am using that is pretty strict about only allowing a single MySQL statement as input so unless I wanted to redesign the whole system (I don't, nor would management want me to spend time on that right now) I have to find a way to make this work in a single MySQL statement.

对我在这里做错的任何想法吗?

Any ideas on what I'm doing wrong here?

推荐答案

有一种解决方法:

CREATE TABLE AS
   SELECT *
   FROM (
       SELECT ...
       UNION ALL
       SELECT ...
   ) AS foo

您不能直接为create table进行联合，但可以将其作为子选择:

You can't do the union directly for the create table, but you can make it a sub-select:

mysql> create table foo as (select * from ((select 'foo') union all (select 'bar')) as foo);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

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