在创建工会之外的临时表格时遇到的问题

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

我有一个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)

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

但是，当我试图用CREATE临时表来包装它时:

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

I get this error:

我得到这个错误:

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

错误1064(42000):在SQL语法中有一个错误;请检查与MySQL服务器版本对应的手册，以获得在“UNION SELECT cust”附近使用的正确语法。cell AS MobileNo, acct.firstname AS FirstName, ac' at line 1

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.

选择语句可以与创建临时表一起工作，只是联合不工作。返回的MySQL错误是模糊的，所以我不知道问题出在哪里。我尝试用括号括住每个SELECT语句，但它也不喜欢这样。

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.

我知道理论上我可以创建临时表使用一个从第二个选择,然后添加数据表,但这解决方案并不是真正可行的在这种情况下,因为它是一个报告生成器我用很严格的只允许一个MySQL声明作为输入所以除非我想重新设计整个系统(我没有,也不会管理要我花时间在现在我必须找到一个方法来做这项工作在一个MySQL声明。

Any ideas on what I'm doing wrong here?

有什么想法吗?

1 个解决方案

#1

Here' a workaround:

这里的一个解决方案:

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

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

不能直接为create表执行union，但可以将它设置为子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

#1