MySQL计数来自多个表的匹配记录 [英] MySQL Count matching records from multiple tables

问题描述

考虑以下4个表格

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

如何确定所有三个表中的实体使用情况，它们表示为

How can I figure out entity usage in all three tables, represented like

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

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

My Original approach was to select from entity then left join other tables but MySQL doesn't seem to like it

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

推荐答案

另一种重写此查询的方式.

Another way to rewrite this query.

分别对每个表进行分组和计数，然后加入:

Group and count in each table individually, then join:

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)上添加索引.

---

You should definitely add an index on (ei) on each of the 3 tables, if you haven't one.

这篇关于MySQL计数来自多个表的匹配记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！