mysql - 从 UNION 创建临时表时遇到问题

Question

我有一个 UNION 语句，它自己执行得很好:

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 包装它时:

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

我收到这个错误:

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 语句包装在括号中，但它也不喜欢那样。

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

关于我在这里做错了什么有什么想法吗？

Answer 1

这里有一个解决方法:

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

你不能直接为 create table 做联合，但你可以让它成为一个子选择:

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