sql - 两个表的sql视图

Question

我有一个具有主键 NID 的 Neighborhood 表和一个具有外键 NeighborhoodID 的 Apartment 表。我如何创建一个视图来显示每个社区有多少公寓。谢谢！

标签： sqlview

Answer 1

我会加入邻居 id 上的表格，按它分组，并计算行数：

CREATE OR REPLACE VIEW neighbourhood_apartments AS
SELECT   n.name, COUNT(*)
FROM     neighbourhood n
JOIN     apartment a ON n.nid = a.neightbourhoodid
GROUP BY n.name