求一条MYSQL语句

[wwwzrccom]

有三张表，
课程: course(cid 主键自增,cname)
问题：topic(tid 主键自增,cid,tname)
回答：revert(rid 主键自增,tid，rname)
求sql:每个课程下的问题数，每个课程下的回答数

[sunnylyy]

1. select cid, count(tid) from topic group by cid

[wwwzrccom]

用一条语句

[wenjjing2lianee]

顶一个

[存在666]

进来看下，学习！

[sunnylyy]

select A.cid, count(A.tid), sum(B.rcount)
from topic A, (select tid, count(rid) as rcount from revert group by tid)B
where A.tid = B.tid group by A.cid

手写的，不保证一定正确。

[a7945127]

up

[fengyuanfa]

SELECT 
	c.cid, 
	COUNT( t.tid ) AS tid_counts, 
	COUNT( r.rid ) AS rid_counts
FROM 
	course c
	LEFT JOIN topic t ON c.cid = t.cid
	LEFT JOIN revert r ON t.tid = r.tid
GROUP BY 
	c.cid