MySQL 使用 MS Access Union Query 合并 3 个表/查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4629659/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Merging 3 tables/queries using MS Access Union Query
提问by RyanKDalton
I have built a MySQL database to store bill payments. Everyone in my office has MS Access, so I am building a front-end database reporting tool using MS Access and linking to the MySQL tables on backend.
我已经建立了一个 MySQL 数据库来存储账单支付。我办公室里的每个人都有 MS Access，所以我正在使用 MS Access 构建一个前端数据库报告工具，并链接到后端的 MySQL 表。
I have created some Access queries that reference the MySQL tables, done some manipulation, and now want to merge three queries (with the same table structure) back into one that I can build my report on.
我创建了一些引用 MySQL 表的 Access 查询，做了一些操作，现在想要将三个查询（具有相同的表结构）合并回一个我可以构建我的报告的查询。
Through my research (article1, article2, and others) , I have found that a Union query is what I need. I can union 2 tables just fine but when I try to union the 3rd, the query fails to execute. I have tested the Union query on each combination individually, (1-2, 1-3, 2-3) and any pair works. I am trying to understand what I might be doing wrong in order to incorporate the 3rd query into a single Union. Can you offer any suggestions?
通过我的研究（第一条，第二条，和其他人），我发现，联合查询正是我需要的。我可以合并 2 个表就好了，但是当我尝试合并第三个表时，查询无法执行。我已经分别测试了每个组合的联合查询（1-2、1-3、2-3）并且任何对都有效。为了将第三个查询合并到一个联合中，我试图了解我可能做错了什么。你能提供任何建议吗？
Table 1 = A Table 2 = B Table 3 = C
表 1 = A 表 2 = B 表 3 = C
SELECT A.Year, A.BillingQuarter, A.Name, A.ObjectCode, A.Amount
FROM A
UNION ALL SELECT B.Year, B.BillingQuarter, B.Name, B.ObjectCode, B.Amount
FROM B
UNION ALL SELECT C.Year, C.BillingQuarter, C.Name, C.ObjectCode, C.Amount
FROM C
;
;
* UPDATE *After exporting each query to standalone tables, I was able to run a 3-table UNION ALL query and merge them together. So the problem clearly lies in my attempt to UNION 3 queries, not in 3 tables. Thoughts?
* 更新 *将每个查询导出到独立表后，我能够运行 3 表 UNION ALL 查询并将它们合并在一起。所以问题显然在于我尝试 UNION 3 查询，而不是 3 个表。想法？
Thanks!
谢谢！
采纳答案by Paul McCowat
I can't test this in Access but it works in SQL Server, Select the first two tables with a UNION as a derived table then UNION table C and the derived table.
我无法在 Access 中对此进行测试，但它在 SQL Server 中有效，选择带有 UNION 作为派生表的前两个表，然后选择 UNION 表 C 和派生表。
SELECT Year, BillingQuarter, Name, ObjectCode, Amount FROM
(SELECT Year, BillingQuarter, Name, ObjectCode, Amount FROM @A
UNION ALL
SELECT Year, BillingQuarter, Name, ObjectCode, Amount FROM B)
AS Derived
UNION ALL
SELECT Year, BillingQuarter, Name, ObjectCode, Amount FROM C
It may be worth looking at the schema design / relationships to see if this can be avoided.
可能值得查看模式设计/关系，看看是否可以避免这种情况。
回答by Steven Dey
You need a semicolon (;) to end the query. See below:
您需要一个分号 (;) 来结束查询。见下文：
SELECT Year, BillingQuarter, Name, ObjectCode, Amount FROM
(SELECT Year, BillingQuarter, Name, ObjectCode, Amount FROM @A
UNION ALL
SELECT Year, BillingQuarter, Name, ObjectCode, Amount FROM B)
AS Derived
UNION ALL
SELECT Year, BillingQuarter, Name, ObjectCode, Amount FROM C;
