先看例子：
表A
+----+-------+
| id | name |
+----+-------+
| 1 | jason |
| 2 | tom |
| 3 | jack |
| 4 | mathy |
+----+-------+

表log_1
+----+------+
| id | A_id |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
| 4 | 1 |
| 5 | 3 |
| 6 | 2 |
+----+------+

表log_2
+----+------+
| id | A_id |
+----+------+
| 1 | 4 |
| 2 | 2 |
| 3 | 3 |
| 4 | 3 |
| 5 | 4 |
| 6 | 1 |
| 7 | 3 |
| 8 | 2 |
+----+------+

想同时查询表A的各条数据在log_1和log_2中的出现次数统计，用以下sql代码查询

SELECT A . * , COUNT( b.id ) AS log_1_num, COUNT( c.id ) AS log_2_num
FROM A
LEFT JOIN log_1 b ON A.id = b.A_id
LEFT JOIN log_2 c ON A.id = c.A_id
GROUP BY A.id

结果如下：

+----+-------+-----------+-----------+
| id | name | log_1_num | log_2_num |
+----+-------+-----------+-----------+
| 1 | jason | 3 | 3 |
| 2 | tom | 4 | 4 |
| 3 | jack | 3 | 3 |
| 4 | mathy | 0 | 2 |
+----+-------+-----------+-----------+

看到此数据第一反映是他肯定有问题，因为log_1_num的总和和log_1表的条数并不相等，仔细看发现是第二次left join的时候将log_2的表里数据与第一次的left join做了笛卡尔乘，所以出现了m*n 的效果，多半都要做 (m*n)/m才能得出正确的数值；

但是看官方的手册里给distinct可以解决次问题，即

SELECT A.*,COUNT(DISTINCT b.id) AS log_1_num,COUNT(DISTINCT c.id) AS log_2_num
FROM A
LEFT JOIN log_1 b ON A.id=b.A_id
LEFT JOIN log_2 c ON A.id=c.A_id
GROUP BY A.id

结果如下，
+----+-------+-----------+-----------+
| id | name | log_1_num | log_2_num |
+----+-------+-----------+-----------+
| 1 | jason | 3 | 1 |
| 2 | tom | 2 | 2 |
| 3 | jack | 1 | 3 |
| 4 | mathy | 0 | 2 |
+----+-------+-----------+-----------+

当然，distinct的查询效率貌似不怎么样，但是这是我想到的比较好的办法了；

当然还有另一种办法，那就是用A表分别和log_1和log_2左关联，然后利用将结果合并，貌似那样也不咋滴吧！

Tags: count, left join, m*n, mysql