Question

mysql count 多表联查问题

今题做多表联查count值时出现的问题，2个表分开查count值都正确，但是一联查，就出现问题了 如下：

查表1count值

mysql> select count(d.p_rel_id) from a_area AS a left join p_permission_door AS d ON a.a_id=d.p_to where a.a_id=1;
+-------------------+
| count(d.p_rel_id) |
+-------------------+
| 21 |
+-------------------+
查表2的count值

mysql> select count(c.p_rel_id) from a_area AS a left join p_permission_car AS c ON a.a_id=c.p_to where a.a_id=1;
+-------------------+
| count(c.p_rel_id) |
+-------------------+
| 24 |
+-------------------+
到这都分开查都没有问题，但是联查如下
mysql> select count(d.p_rel_id),count(c.p_rel_id) from a_area AS a right join p_permission_door AS d ON d.p_to=a.a_id right join p_permission_car AS c ON c.p_to=a.a_id where a.a_id=1;
+-------------------+-------------------+
| count(d.p_rel_id) | count(c.p_rel_id) |
+-------------------+-------------------+
| 504 | 504 |
+-------------------+-------------------+
不知怎么查出的结果就这样了 后来发现540=21*24 就晕了
不是540 是504=21*24

Answer 1

a right join d right join c====(a right join d) right join c
你的目的是找c表中能在a表找到对应值记录的数量 找d表中能在a表找到对应值记录的数量
但是你最后一句的涵义是a表中能在d表和c表同时找到对应值的数量，此时count(c.p_rel_id)其实是你c表的数量，就是那个504的涵义，至于count(d.p_rel_id)为什么等于504 完全是凑巧~~~

追问

那请问下这个应该怎么改，或者说mysql就没办法这么查

追答

可以啊 用join 可以实现, sql如下select dcount,ccount from (select count(d.p_rel_id) as dcount from a_area AS a left join p_permission_door AS d ON a.a_id=d.p_to where a.a_id=1) t1 join (select count(c.p_rel_id) as ccount from a_area AS a left join p_permission_car AS c ON a.a_id=c.p_to where a.a_id=1) t2