计算char在已发生少于多次的表中出现的次数

I'm trying to replicate this table:

我正在尝试复制此表：

which is to list all the names of the cities that have less than 2 manufacturer factories. I've tried to implement these methods through the use of HAVING(*) and mixing it with INNER JOIN, but I'm not exactly sure If I'm on the right track.

这是列出少于2个制造商工厂的城市的所有名称。我试图通过使用HAVING（*）并将其与INNER JOIN混合来实现这些方法，但我不确定我是否在正确的轨道上。

I have tried to code in 2 different ways through looking to other questions on stack:

我试图通过查看堆栈上的其他问题以两种不同的方式编写代码：

1.

1。

SELECT c.CityName as "City Name", m.CityID as "Number of Manufacturers"
FROM CITY4787 c 
INNER JOIN MANUFACTURER4787 m 
ON c.CityID = m.CityID
WHERE m.ManName IN (SELECT m.ManName FROM MANUFACTURER4787
GROUP BY m.ManID HAVING COUNT (ManID) > 2);

The results aren't accurate but if I change the (ManID) to less than 2, there won't be any results.

结果不准确，但如果我将（ManID）更改为小于2，则不会有任何结果。

2.

2。

 SELECT CityID, COUNT(CityID) Counts
  FROM MANUFACTURER4787 
 GROUP BY CityID
HAVING COUNT(CityID) < 2;

The result manages to count cityids that have appeared less than 2 but it is still not right.

结果设法计算出现少于2但仍然不对的城市人。

I don't really understand how to merge the two or if im using these sql statements right. Here is the ERD : ERD

我真的不明白如何合并这两个或如果我正确使用这些SQL语句。这是ERD：ERD

2 个解决方案

#1

If I unserstand correct.you can try this.

如果我unserstand正确。你可以试试这个。

If that doesn't spot you expect,Could you provide some sample data I will edit my answer.

如果您没想到，可以提供一些示例数据，我会编辑我的答案。

SELECT c.CityName as "City Name", COUNT(m.ManID) as "Number of Manufacturers"
FROM CITY4787 c 
LEFT JOIN MANUFACTURER4787 m ON c.CityID = m.CityID
GROUP BY c.CityName
HAVING COUNT(m.ManID) < 2

#2

Based on your schema, I think this should work:

根据您的架构，我认为这应该工作：

SELECT c.CityName as "City Name", COUNT(m.ManName) AS "Number of Manufacturers"
FROM City c
LEFT JOIN Manufacturer m
ON m.CityID = c.CityID
GROUP BY c.CityID
HAVING COUNT(m.ManName) < 2