MySQL中为每个人计算房屋

Question

我有两张桌子：Person和House。每个人都有一个名字、电子邮件和电话号码，而一所房子有多个房间和一个城市位置。我必须显示每个人的名字和房子的数量，其中房子的数量就是一个人拥有多少房子的数量。

这似乎是一个有很多关系的人。下面是我对人员表的查询：

CREATE TABLE Person ( person_id INT NOT NULL PRIMARY KEY, 
       name VARCHAR(15) NOT NULL, 
       email VARCHAR(320) NOT NULL,
       phone_number VARCHAR(15) NOT NULL);

这是House Table的查询：

CREATE TABLE House (
    house_id INT NOT NULL PRIMARY KEY,
    person_id INT NOT NULL,
    number_of_rooms INT NOT NULL,
    city_location VARCHAR(15) NOT NULL,
    FOREIGN KEY(person_id) REFERENCES Person(person_id) );

为了显示每个人有多少房子，我写了这个查询：

SELECT name AS "Person name", count(house_id) AS "Number of houses" FROM Person JOIN House WHERE Person.person_id = House.person_id;

但该查询只显示我插入的第一个名称（插入更多）和该名称的house_id之和（对于house_id#1&#2&#3，结果应该是3，但它显示6），仅此而已

当我第一次想到它时，它真的很容易构建，我不知道为什么我要在这上面浪费这么多时间。有什么帮助吗？

Answer 1

SELECT p.name AS "Person name", count(h.house_id) AS "Number of houses" 
FROM Person p
LEFT JOIN House h ON h.person_id = p.person_id
GROUP BY p.name

这正是你所需要的。

我改变了以下内容：

• 为表添加别名（很方便）
• 使用LEFT OUTER JOIN（简称LEFT JOIN）而不是INNER JOIN（简称JOIN），以获得甚至没有匹配房子的人（他们的房子数量将为零）
• 为JOIN添加了ON子句（语法要求）
• 添加了GROUP BY子句。使用COUNT等聚合函数时需要

Answer 2

您需要一个group by。而且，在使用JOIN时，应该使用ON子句。我还建议使用表别名。它们使查询更易于编写和读取：

SELECT p.name AS "Person name", count(h.house_id) AS "Number of houses"
FROM Person p JOIN
     House h
     ON p.person_id = h.person_id
GROU PBY p.name;