mysql - 在sql中使用for语句从2个表中选择

Question

我有2个表，想从中选择数据

table 1 :
id
name

table 2
id
name
table1.id

我想要一个查询来产生这个结果:

table1.id
table1.name
count(table2.id)

这很简单，可以通过这种方式解决:

SELECT 
c.id as corridor_id,
c.name as corridor_name,
(SELECT COUNT( r.id ) FROM rooms AS r WHERE r.corridorid = c.id ) as room_count
FROM corridors AS c

现在如果我像这样添加另一个表:

table3
id
name
table2.id

并想要这样的查询:

table1.id
table1.name
count(table2.id)
count(table3.id)

idk 我该怎么做这个查询，但如果有办法我会很乐意找到它，很多 tnx

Answer 1

您需要将它们全部连接在一起，然后按照以下方式对它们进行分组:

SELECT 
   t1.Id,
   t1.Name,
   Count(t2.Id) AS T2Count,
   Count(t3.Id) AS T3Count 
FROM table1 t1
JOIN table2 t2
ON t1.Id = t2.table1_id
JOIN table3 t3
ON t2.id = t3.table2_id
GROUP BY t1.Id, t1.Name