在 SQL 查询中同时使用 union 和 count(*)

Question

我有一个 SQL 查询，看起来像这样：

select name, count (*) from Results group by name order by name

和另一个相同的查询，它从存档结果表加载，但字段是相同的。

select name, count (*) from Archive_Results group by name order by name

我如何将两者合并到一个查询中？ （所以分组仍然可以正常工作）。 我尝试使用 union all，但它不起作用。 我缺少什么？

Answer 1

如果您有支持索引和相对较高的计数，类似这样的方法可能比建议的解决方案要快得多：

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;

Answer 2

您的目标是...

1. 统计“Bob”的所有实例
   两个表中的“Jones”（例如）
2. 计算“Bob”的所有实例
   Jones”在 Results 中的一行中，并且
   “Bob Jones”的所有实例
   Archive_Results 在单独的行中？

假设它是#1，你会想要这样的东西......

SELECT name, COUNT(*) FROM
(SELECT name FROM Results UNION ALL SELECT name FROM Archive_Results)
GROUP BY name
ORDER BY name

Answer 3

SELECT tem.name, COUNT(*) 
FROM (
  SELECT name FROM results
  UNION ALL
  SELECT name FROM archive_results
) AS tem
GROUP BY name
ORDER BY name

Answer 4

select T1.name, count (*)
from (select name from Results 
      union 
      select name from Archive_Results) as T1
group by T1.name order by T1.name

Answer 5

SELECT SUM(*) 
FROM (
  SELECT COUNT(*) AS cnt FROM IP_IRRADIATE_SUBMISSION_PHOTON WHERE IRRADIATESUBMISSIONMASTERID = 84                        
  UNION SELECT COUNT(*) AS cnt FROM IP_IRRADIATE_SUBMISSION_ELECTRON WHERE IRRADIATESUBMISSIONMASTERID = 84
);

这在 Oracle 中有效。 “作为TMP”正在扔它。 稍微不同的查询是对几行计数进行求和。