56,677
社区成员
发帖
与我相关
我的任务
分享
mysql> select * from a;
+-----+--------+
| cid | name |
+-----+--------+
| 1 | baidu |
| 2 | google |
+-----+--------+
2 rows in set (0.00 sec)
mysql> select * from b;
+-----+-----------+------+
| sid | name | cid |
+-----+-----------+------+
| 1 | tb.baidu | 1 |
| 2 | ta.baidu | 1 |
| 3 | te.google | 2 |
| 4 | tq.google | 2 |
+-----+-----------+------+
4 rows in set (0.00 sec)
mysql> select * from c;
+----+------+------+
| id | name | sid |
+----+------+------+
| 1 | aa | 1 |
| 2 | bb | 1 |
| 3 | cc | 2 |
| 4 | dd | 3 |
| 5 | qq | 4 |
| 6 | ww | 4 |
| 41 | tt | 4 |
+----+------+------+
7 rows in set (0.00 sec)
mysql>
mysql> select a.cid,a.name,count(distinct b.sid),count(c.id)
-> from a left join b on a.cid=b.cid
-> left join c on b.sid=c.sid
-> group by a.cid,a.name;
+-----+--------+-----------------------+-------------+
| cid | name | count(distinct b.sid) | count(c.id) |
+-----+--------+-----------------------+-------------+
| 1 | baidu | 2 | 3 |
| 2 | google | 2 | 4 |
+-----+--------+-----------------------+-------------+
2 rows in set (0.01 sec)
mysql>
select a.*,count(distinct b.sid),count(c.id)
from a left join b on a.cid=b.cid
left join c on b.sid=c.sid