如何BY子句同时使用加入了MySQL表

Question

我有2个表中的MySQL应用具有旅游，团体 - 问表

QuestionID | QuestionName 
-----------|--------------------------------------- 
1   | How is your faculty communication 
-----------|--------------------------------------- 
2   | How is your study matrial 
-----------|--------------------------------------- 
3   | How your faculty language 
-----------|--------------------------------------- 
4   | Is your faculty cooperative 
-----------|--------------------------------------- 
5   | Is your practical time is enough 
-----------|--------------------------------------- 
6   | Your class starts on time 
-----------|--------------------------------------- 
7   | In practical your doubts are cleared properly 
-----------|--------------------------------------- 
8   | What will you rate for computer 
-----------|--------------------------------------- 
9   | In Theory your questions are replied properly 
-----------|--------------------------------------- 
10   |Your faculty is comes on time 
-----------|--------------------------------------- 

结果表

RID QID Faculty   Student   Sem Excell Better Good Poor 
1 1 Ankush   Vishal Deb  III 1  0  0 0 
2 2 Ankush   Vishal Deb  III 0  1  0 0 
3 3 Ankush   Vishal Deb  III 0  0  1 0 
4 4 Ankush   Vishal Deb  III 0  0  0 1 
5 5 Ankush   Vishal Deb  III 0  0  1 0 
6 6 Ankush   Vishal Deb  III 0  1  0 0 
7 7 Ankush   Vishal Deb  III 1  0  0 0 
8 8 Ankush   Vishal Deb  III 0  1  0 0 
9 9 Ankush   Vishal Deb  III 0  0  1 0 
10 10 Ankush   Vishal Deb  III 0  0  0 1 
11 1 Mahendra Singh Mohit Chauhan III 0  1  0 0 
12 2 Mahendra Singh Mohit Chauhan III 0  0  1 0 
13 3 Mahendra Singh Mohit Chauhan III 0  1  0 0 
14 4 Mahendra Singh Mohit Chauhan III 0  0  0 1 
15 5 Mahendra Singh Mohit Chauhan III 0  1  0 0 
16 6 Mahendra Singh Mohit Chauhan III 0  0  1 0 
17 7 Mahendra Singh Mohit Chauhan III 1  0  0 0 
18 8 Mahendra Singh Mohit Chauhan III 0  0  0 1 
19 9 Mahendra Singh Mohit Chauhan III 0  1  0 0 
20 10 Mahendra Singh Mohit Chauhan III 0  0  0 1 

现在我需要显示的记录特定学期的特定教师的报告，但该报告应该显示该学期的学生得到的优秀，更好，好和穷人的总数。

例如，如果从学期III提交反馈5学生为Ankush然后报告应该来像 - 我给例如为4个问题

---------------------------------------------------------------------+ 
Question       |Excellent | Better | Good | Poor | 
-----------------------------------|----------|--------|------|------| 
How is your faculty communication | 3  | 2 | 0 | 0 | 
-----------------------------------|----------|--------|------|------| 
How is your study matrial   | 1  | 1 | 3 | 0 | 
-----------------------------------|----------|--------|------|------| 
How your faculty language   | 0  | 1 | 3 | 1 | 
-----------------------------------|----------|--------|------|------| 
Is your faculty cooperative  | 1  | 1 | 2 | 1 | 
-----------------------------------|----------|--------|------|------| 

我尝试这样的查询，但是这不是我所需要的

SELECT q.questionname, r.excellent, r.better, r.good, r.poor 
FROM question q, result r 
WHERE r.facultyid = 'Ankush' 
AND r.Semester = 'III' 
AND q.questionID = r.questionID 

也试过

Select q.questionname, sum(r.excellent),sum(r.better),sum(r.good),sum(r.poor) 
from question q,result r 
where r.facultyid='Ankush' and r.Semester='III' and q.questionID=r.questionID; 

但没有成功。请引导我如何获得我的结果。 预先感谢您。

Answer 1

你的第二个查询是非常接近 - 你只是离开了group by条款：

Select q.questionname, sum(r.excellent),sum(r.better),sum(r.good),sum(r.poor) 
from question q 
    inner join result r on q.questionID=r.questionID 
where r.facultyid='Ankush' 
    and r.Semester='III' 
group by q.questionname 

也请注意，这个使用显式join。一般来说，我会建议在from条款中不使用逗号。