mysql - 多个 UNION 查询不起作用

Question

多 UNION 查询:

SELECT [Ordine numero] AS ordine, [data ordine] AS data, comm AS commessa
FROM [archivio globale]
WHERE [ordine numero] IS NOT NULL

UNION

SELECT [numero ordine cliente] AS ordine, [data ordine cliente] AS data, numero AS commessa
FROM [ricambi]
WHERE [numero ordine cliente] IS NOT NULL

UNION

SELECT [numero ordine cliente] AS  ordine, [data ordine cliente] AS data, numero AS commessa
FROM [trasferte]
WHERE [numero ordine cliente] IS NOT NULL

ORDER BY [ordine];

它不起作用:我收到错误消息:

[mysql][odbc 5.1 Driver][mysqld-5.5.14]你的SQL语法有错误...

虽然单个 UNION 查询正常工作(没有第二个 UNION 语句)？怎么办？

Answer 1

您已将您的问题标记为 MySQL，并且您正在使用方括号 []。据我所知，方括号不对 MySQL 有效，仅对 Microsoft 产品 (SQL Server/MS Access) 有效。因此，如果您需要包含表名/列名，请使用反引号 `。

From the documentation:

The identifier quote character is the backtick (“`”):

所以我认为您的查询应该是:

SELECT `Ordine numero` AS ordine, `data ordine` AS data, comm AS commessa
FROM `archivio globale`
WHERE `ordine numero` IS NOT NULL

UNION ALL

SELECT `numero ordine cliente` AS ordine, `data ordine cliente` AS data, numero AS commessa
FROM `ricambi`
WHERE `numero ordine cliente` IS NOT NULL

UNION ALL

SELECT `numero ordine cliente` AS  ordine, `data ordine cliente` AS data, numero AS commessa
FROM `trasferte`
WHERE `numero ordine cliente` IS NOT NULL

ORDER BY `ordine`;

编辑，如果您使用的是 MS Access，则需要使用方括号:

SELECT *
FROM
(
  SELECT [Ordine numero] AS ordine, [data ordine] AS data, comm AS commessa
  FROM [archivio globale]
  WHERE [ordine numero] IS NOT NULL

  UNION ALL

  SELECT [numero ordine cliente] AS ordine, [data ordine cliente] AS data, numero AS commessa
  FROM [ricambi]
  WHERE [numero ordine cliente] IS NOT NULL

  UNION ALL

  SELECT [numero ordine cliente] AS  ordine, [data ordine cliente] AS data, numero AS commessa
  FROM [trasferte]
  WHERE [numero ordine cliente] IS NOT NULL
) x
ORDER BY [ordine];