mysql union 出错,mysql – 从UNION创建临时表时遇到问题

我有一个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创建临时表,然后将第二个数据添加到表中,但是在这种情况下该解决方案实际上并不可行,因为它是我正在使用的报表生成器很漂亮严格只允许一个MySQL语句作为输入,所以除非我想重新设计整个系统(我没有,管理层也不希望我现在花时间在这上面)我必须找到一种方法来使这个工作在一个单个MySQL语句.

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

解决方法:

这是一个解决方法：

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

标签：mysql,union,temp-tables

来源： https://codeday.me/bug/20190623/1273027.html