我有2个MySQL表:people&connections
我想从人员中选择所有列,并计算他们在连接表中的连接数。
-------------------- ----------------------
| People | | Connections |
-------------------- ----------------------
| person_id | name | | cnt_id | person_id |
-------------------- ----------------------
我想从人员中选择所有人员,并选择该人员的person_id在第二个表中出现的次数。
我一直在试图弄清楚,但不能。希望你们都能提供帮助。
谢谢!
如果有一个人没有连接,这将给出一个计数为零的行:
SELECT
People.person_id,
People.name,
COUNT(Connections.person_id) AS number_of_connections
FROM People
LEFT JOIN Connections
ON People.person_id = Connections.person_id
GROUP BY People.person_id
试试这个
SELECT name,count(Connection.person_id) as cnt FROM
People LEFT JOIN Connection ON People.person_id = Connection.person_id
GROUP BY Connection.person_id