MySQL Count 来自多个表的匹配记录

Question

考虑以下4个表

entity  table1        table2        table3       
------  ------------- ------------- -------------
id      ei(entity.id) ei(entity.id) ei(entity.id)
name    something     somethingelse yetanother

如何找出所有三个表中的实体用法，表示方式

---------------------
| id | t1 | t2 | t3 |
---------------------
|  1 | 14 | 23 |  0 |
|  2 | 66 |  9 |  5 |
...

我最初的方法是从实体中选择然后左连接其他表，但 MySQL 似乎不喜欢它

SELECT e.id,count(t1.id) FROM entity AS e LEFT JOIN table1 AS t1 on e.id=t1.ei;

编辑:这是 1 个表的输出

mysql> explain select e.id,count(o.id) from entity e left join table1 t1 on e.id=o.ei where e.ty=3;+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+|  1 | SIMPLE      | e     | ALL  | NULL          | NULL | NULL    | NULL |  1083 | Using where ||  1 | SIMPLE      | o     | ALL  | NULL          | NULL | NULL    | NULL | 90201 |             |+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+2 rows in set (0.04 sec)

The opposite works much better, but doesn't scale to multiple tables

SELECT e.id,count(t1,id) FROM table1 AS t1 LEFT JOIN entity AS e ON t1.ei=e.id

Answer 1

重写此查询的另一种方法。

在每个表中单独分组和计数，然后加入:

SELECT  a.id, 
        COALESCE(b.t1, 0) AS t1,
        COALESCE(c.t2, 0) AS t2,
        COALESCE(d.t3, 0) AS t3
FROM
        entity a
    LEFT JOIN
        ( SELECT ei,
                 COUNT(*) AS t1
          FROM table1
          GROUP BY ei
        ) AS b
            ON a.id = b.ei
    LEFT JOIN
        ( SELECT ei,
                 COUNT(*) AS t2
          FROM table2
          GROUP BY ei
        ) AS c
            ON a.id = c.ei
    LEFT JOIN
        ( SELECT ei,
                 COUNT(*) AS t3
          FROM table3
          GROUP BY ei
        ) AS d
            ON a.id = d.ei
  ;

---

如果您还没有的话，您绝对应该在 3 个表中的每一个的 (ei) 上添加一个索引。