如何加入3个表并获得2个不同条件的字段？

Question

我有3个表格：学生，课程（重要领域点），学生课程（重要领域成绩）。

如果成绩大于或等于70，则考试通过，因此学生将获得分数。如果成绩低于70，学生将不会获得分数。如果学生的学分之和大于10，他就毕业了。

我需要选择学生姓名，他的通过分数的总和，他所有成绩的平均值。我可以选择学生姓名，他的通过分数和他通过成绩的平均数之和。但我需要所有成绩的平均值。

我写了下面的查询。

SELECT s.Name, SUM(c.CreditPoints) AS CreditPoints, AVG(sc.Grade) AS 
AverageGrade FROM Students s JOIN StudentCourse sc
ON s.Id = sc.StudentId
JOIN Courses c ON c.Id = sc.CourseIdenter code here
WHERE sc.Grade >= 70
GROUP BY s.Name
HAVING SUM(CreditPoints) > 10
ORDER BY CreditPoints

Answer 1

不知道你面临什么问题。对我来说，我能看到的唯一问题是GROUP BY s。因为多个学生可能有相同的名字，并且有可能从输入集中得到意想不到的结果。您最好使用s.ID作为GROUP，如下所示-

SELECT 
....
GROUP BY s.Id  -- Change the grouping Key
HAVING SUM(CreditPoints) > 10
ORDER BY CreditPoints

Answer 2

使用条件聚合:

SELECT s.Name,
       SUM(CASE WHEN sc.Grade >= 70 THEN c.CreditPoints ELSE 0 END) AS CreditPoints_passing,
       AVG(CASE sc.Grade >= 70 THEN sc.Grade END) AS AverageGrade_passing
       AVG(sc.Grade) AS AverageGrade
FROM Students s JOIN
     StudentCourse sc
     ON s.Id = sc.StudentId JOIN
     Courses c ON c.Id = sc.CourseIdenter code here
GROUP BY s.Name
HAVING CreditPoints_passing > 10  -- I think you want only 10 passing credits here
ORDER BY CreditPoints;

Answer 3

尽管您只想选择学生的姓名，但请确保您的也按学生id分组（我相信studenttable中有一列），因为以太会有两个同名的学生
您不能使用此条件：

WHERE sc.Grade >= 70

因为这将删除带有等级的行

select 
  s.Id, s.Name,
  sum(case when sc.grade >= 70 then c.creditpoints else 0 end) CreditPoints,
  avg(sc.grade) AverageGrade 
from students s 
inner join studentcourse sc on s.id = sc.studentid 
inner join courses c on c.id = sc.courseid
group by s.id, s.Name
having sum(case when sc.grade >= 70 then c.creditpoints else 0 end) > 10
order by CreditPoints desc;

由于您使用的是SQL Server，因此不能在＜code＞HAVING＜/code＞子句中使用别名＜code＞CreditPoints＜/code＞，因此必须重复＜code＞CASE＜/code>语句。