根据另一个表中的值查找总数 [英] Find total count based of values from another table

问题描述

在Mysql中:

我在表中有重复值的城市:

I have cities in table with duplicate values:

表城市:

Name

New York, USA
New York, USA
Chicago, USA
Chicago, USA
Chicago, USA
Paris, France
Nice, France
Milan, Italy

本表数据格式为:

<city>, <country>

同:

<city><comma><space><country>

表格国家:

Name

USA
France
Italy

我想知道每个国家有多少个城市.喜欢:

I want to get how many cities each country has. Like:

Country      Count

USA           2
France        2
Italy         1

所以，我有 2 个查询:

So, I have 2 queries:

SELECT count(*) FROM `Cities` WHERE Cities.name LIKE '%, USA%'

但是这个值USA应该来自:

SELECT * FROM `country`

现在，我如何获得所需的表?

Now, how do I get the desired table?

推荐答案

使用 LEFT JOIN:

select t1.name Country, count(distinct t2.name) Count
from country t1 left join cities t2 
on t2.name like concat('%, ', t1.name)
group by t1.name

查看演示.
结果:

See the demo.
Results:

| Country | Count |
| ------- | ----- |
| France  | 2     |
| Italy   | 1     |
| USA     | 2     |

这篇关于根据另一个表中的值查找总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！