Question

mysql 通过一个字段查询与之对应的另一个表里的数据总数

例如：
a表
id name
1 li
2 wang
3 chou
B表
id p_id name
1 1 liu
2 1 zhang
C表
id p_id name
1 1 wu
2 1 zhao
B表和C表的p_id 都为A表的id 现在想得到a表所有数据和a表id对应下的B，C表的数据的总数 并按a表Id排序 求指教。。
大概最后结果为
id name count(b.id) count(c.id)
1 li 2 2

Answer 1

1、创建表
create table a (name varchar(20) not null,sex varchar(10));
create table b (name varchar(20) not null,sex varchar(10));
2、插入值
insert into a values('john','male');insert into b values('john','male');
insert into a values('kate','female');insert into b values('kate','female');
3、查询方法1
mysql> select name,sex from a where name in (select name from b where name='john');
+------+------+
| name | sex |
+------+------+
| john | male |
+------+------+
1 row in set (0.00 sec)
4、查询方法2
mysql> select a.name,a.sex from a,b where a.name=b.name and b.name='john';
+------+------+
| name | sex |
+------+------+
| john | male |
+------+------+
1 row in set (0.00 sec)

Answer 2

select
*,

(select count(*) from b where b.p_id = a.id) AS count_b,

(select count(*) from c where c.p_id = a.id) AS count_c

from a order by a.id

追问

select a.id as id, a.name as name, count( distinct b.id) as count_ip,count( distinct c.id) as count_cabinet from tabmachinerooms left JOIN b ON a.id=b.p_id '.
' left JOIN cON c.p_id=a.id '.
' GROUP BY a.id'.
' ORDER BY a.id DESC';

我想到这种方法了 数据也是对的 你那种也对 应该用那种呢

追答

我只测试了连接一个表的，结果一样。不过我觉得如果连接两个表的话，我想你那个可能在表连接上有额外的开销。

追问

哦，谢谢啦~~~~

追答

不客气~~~~~~~~~~~