我有一个 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):您的 SQL 语法有错误;检查与您的 MySQL 服务器版本相对应的手册,了解在第 1 行的“UNION SELECT cust.cellp AS MobileNo, acct.firstname AS FirstName, ac”附近使用的正确语法
任一 SELECT 语句与其周围的 CREATE TEMPORARY TABLE 都可以正常工作,只是 UNION 不起作用。返回的 MySQL 错误很模糊,所以我不太清楚问题是什么。我尝试将每个 SELECT 语句括在括号中,但它也不喜欢这样。
我知道理论上我可以使用一个 SELECT 创建临时表,然后将第二个 SELECT 中的数据添加到表中,但在这种情况下该解决方案实际上并不可行,因为它是我正在使用的一个报告生成器,非常漂亮严格要求只允许单个 MySQL 语句作为输入,因此除非我想重新设计整个系统(我不想,管理层也不希望我现在花时间在这上面),否则我必须找到一种方法来使这项工作在单个 MySQL 语句。
关于我在这里做错了什么有什么想法吗?