php - 加入三个表并计数

Question

这个真的很复杂，那我就举个例子，方便大家理解。

假设我们有 4 个表:车库、汽车、证券、地点。

garages在哪里可以找到 cars , securities是确保汽车在车库内安全的安全措施，places在那里你可以找到与那个类似的车库。然后我们就有了一张这样的 table 。

车库表:

-----------------------------------
|garage_id|garage_name|garage_size|
-----------------------------------
|        1|   Garage 1|        200|
-----------------------------------
|        2|   Garage 2|        400|
-----------------------------------

汽车表:

---------------------------
|car_id|car_name|garage_id|
---------------------------
|     1|   Car 1|        1|
---------------------------
|     2|   Car 2|        1|
---------------------------
|     3|   Car 3|        2|
---------------------------

证券表:

----------------------------------
|security_id|security_name|car_id|
----------------------------------
|          1|  Security 1|      1|
----------------------------------
|          2|  Security 2|      1|
----------------------------------
|          3|  Security 3|      2|
----------------------------------
|          4|  Security 4|      3|
----------------------------------

放置表:

-------------------------------
|place_id|place_name|garage_id|
-------------------------------
|       1|   place 1|        1|
-------------------------------
|       2|   place 2|        1|
-------------------------------
|       3|   place 3|        1|
-------------------------------
|       4|   place 4|        2|
-------------------------------
|       5|   place 5|        2|
-------------------------------

我想要的是列出车库并加入汽车、证券和地点这三个表，如下所示:

Garage 1 has 2 cars with 3 securities and has 3 more garages similar

Garage 2 has 1 cars with 1 securities and has 2 more garages similar

现在您可能会问，为什么 Garage 1有3种证券？因为Garage 1有Car 1和 Car 2和 Car 1有两种证券Security 1和 Security 2和 Car 2有 1 个 Security 3 .

这里的问题是:如何在单个查询中连接表和计数并返回结果，就像您在上面看到的那样？

Answer 1

简单地连接所有表，按车库分组并计算不同的匹配项:

select 
  g.garage_name, 
  count(distinct c.car_id) as count_cars,
  count(distinct s.security_id) as count_securities,
  count(distinct p.place_id) as count_places
from garages g
left join cars c on c.garage_id = g.garage_id
left join securities s on s.car_id = c.car_id
left join places p on p.garage_id = g.garage_id
group by g.garage_name
order by g.garage_name;

关于所需的输出字符串，连接您的结果:

g.garage_name || ' has ' || count(distinct c.car_id) || ' cars with ' || ...