mysql - 从2个表中选择带有SQL中的for语句

我有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

最佳答案

您在这里不需要嵌套的SELECT语句。您可以通过分组来避免重复计数，您可以使用DISTINCT关键字：

SELECT
    c.id as corridor_id,
    c.name as corridor_name,
    COUNT(DISTINCT r1.id),
    COUNT(DISTINCT r2.id)
FROM
    corridors c
    JOIN rooms r ON r.corridorid = c.id
    JOIN rooms2 r2 ON r2.corridorid = c.id
GROUP BY c.id

如果要正确处理缺失值（0个计数），也可以执行以下操作：

SELECT
    c.id as corridor_id,
    c.name as corridor_name,
    IFNULL(COUNT(DISTINCT r1.id), 0),
    IFNULL(COUNT(DISTINCT r2.id), 0)
FROM
    corridors c
    LEFT JOIN rooms r ON r.corridorid = c.id
    LEFT JOIN rooms2 r2 ON r2.corridorid = c.id
GROUP BY c.id

关于mysql - 从2个表中选择带有SQL中的for语句，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/16015309/