如何将两个计数查询结合在一起?

I have two tables that I need to get a count of entries per person summed between the two tables.

我有两个表，我需要得到每个人的条目数在这两个表之间的总和。

I need to join the following queries together in one grouped by the name.

我需要将以下查询合并到一个按名称分组的查询中。

select count(entryid) as table1count, reqname1 from table1

And the second query:

第二个查询:

select count(comid) as table2count, reqname2 from table2

How would I put these together so that I ended up with output like this:

我如何将它们组合在一起最终得到这样的输出:

jsmith 236
jsnow 13
etc.

Sometimes the same reqname will be in both tables, but it could only be in one or the other. So I want to show all reqnames between the two tables and their counts summed between the two tables.

有时相同的reqname会出现在两个表中，但只能出现在其中一个或另一个表中。我想展示两个表之间的所有reqname以及两个表之间的计数。

Any ideas on how to do this?

有什么办法吗?

2 个解决方案

#1

You could use a sub query that performs a union all, and then group and count:

您可以使用子查询来执行一个union all，然后进行分组和计数:

select name, count(id) 
from (
        select reqname1 as name, entryid as id from table1
        union all
        select reqname2, comid from table2
     ) as combined
group by name

#2

You can use UNION ALL:

您可以使用UNION ALL:

select count(entryid) as table1count, reqname1 from table1
UNION ALL
select count(comid) as table2count, reqname2 from table2

#1