Question

SQL 三表联合查询,查询一个表的和

分类表：DID，Dname
题目表：TID，DID
题目答题情况表：RID，TID，Istrue（正确与错误辨别字段，正确为1，错误为0）
现在要按分类列出每一个分类的答题情况，也就是每个分类的答题记录的和、正确数记录的和

Answer 1

分类表A 题目表B 答题情况表C
select d.did,d.c1,e.c2 from
( SELECT B.DID COUNT(C.RID) AS C1 FROM C
INNER JOIN B ON B.TID = C.TID
GROUP BY B.DID ) d
left join
( SELECT B.DID COUNT(C.RID) AS C2 FROM C
INNER JOIN B ON B.TID = C.TID
WHERE C.Istrue = 1
GROUP BY B.DID) e on e.did = d.did

Answer 2

select Dname,count(1) as 答题总数,sum(c.Istrue=1) as 答题正确记录数

from 分类表 a,题目表 b,题目答题情况表 c
where a.did=b.did and b.tid=c.tid
group by Dname

Answer 3

每个分类的答题记录的和 (使用的是子查询，当然也可以用联接查询)，正确数记录的和你应该知道怎么弄了吧

select sum(RID) from 题目答题情况表 where TID in
(
select TID from 题目表 where DID in
(select DID from 分类表 group by Dname)
)