在 SQL 查询中一起使用 union 和 count(*)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1266666/
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
Using union and count(*) together in SQL query
提问by David Bo?jak
I have a SQL query, looks something like this:
我有一个 SQL 查询，看起来像这样：
select name, count (*) from Results group by name order by name
and another, identical which loads from a archive results table, but the fields are the same.
另一个，从存档结果表加载相同，但字段是相同的。
select name, count (*) from Archive_Results group by name order by name
How would I combine the two in just one query? (So the group by would still function correctly). I tried with union all, however it won't work. What am I missing?
我如何将两者结合在一个查询中？（因此 group by 仍然可以正常运行）。我尝试与 union all 一起使用，但它不起作用。我错过了什么？
回答by krdluzni
SELECT tem.name, COUNT(*)
FROM (
SELECT name FROM results
UNION ALL
SELECT name FROM archive_results
) AS tem
GROUP BY name
ORDER BY name
回答by Steve Kass
If you have supporting indexes, and relatively high counts, something like this may be considerably faster than the solutions suggested:
如果您有支持索引和相对较高的计数，这样的事情可能比建议的解决方案快得多：
SELECT name, MAX(Rcount) + MAX(Acount) AS TotalCount
FROM (
SELECT name, COUNT(*) AS Rcount, 0 AS Acount
FROM Results GROUP BY name
UNION ALL
SELECT name, 0, count(*)
FROM Archive_Results
GROUP BY name
) AS Both
GROUP BY name
ORDER BY name;
回答by VoteyDisciple
Is your goal...
你的目标是...
- To count all the instances of "Bob Jones" in both tables (for example)
- To count all the instances of "Bob
Jones" in
Resultsin one row and all the instances of "Bob Jones" inArchive_Resultsin a separate row?
- 计算两个表中“Bob Jones”的所有实例（例如）
- 要
Results在一行中计算“鲍勃·琼斯”的所有实例，并Archive_Results在单独的一行中计算“鲍勃·琼斯”的所有实例 ？
Assuming it's #1 you'd want something like...
假设它是#1，你会想要像......
SELECT name, COUNT(*) FROM
(SELECT name FROM Results UNION ALL SELECT name FROM Archive_Results)
GROUP BY name
ORDER BY name
