sql - SELECT 中多个聚合的优化

Question

我读过 Microsoft T-SQL Performance Tuning whitepaper就大型表的性能而言，相关的子查询可能代价高昂:

...Compare this to the firstsolution that would scan the whole table and execute a correlated subquery for everyrow. The difference in performance is negligible on a small table. But on a large table itmay amount to hours of processing time...

是否有一种通用方法可以将具有基于不同标准的多个聚合的查询作为相关子查询转换为使用 JOIN 的单个查询？ s 而不是相关的子查询？
考虑一个例子:
准备架构:

CREATE TABLE Student (
    ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(255) NOT NULL
);

CREATE TABLE Grade (
    ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    StudentID INT NOT NULL FOREIGN KEY REFERENCES Student(ID),
    Score INT NOT NULL,
    CONSTRAINT CK_Grade_Score CHECK (Score >= 0 AND Score <= 100)
);

INSERT INTO Student (Name) VALUES ('Steven');
INSERT INTO Student (Name) VALUES ('Timmy');
INSERT INTO Student (Name) VALUES ('Maria');
 
INSERT INTO Grade (StudentID, Score) VALUES (1, 90);
INSERT INTO Grade (StudentID, Score) VALUES (1, 81);
INSERT INTO Grade (StudentID, Score) VALUES (1, 82);
INSERT INTO Grade (StudentID, Score) VALUES (1, 82);

INSERT INTO Grade (StudentID, Score) VALUES (2, 99);
INSERT INTO Grade (StudentID, Score) VALUES (2, 63);
INSERT INTO Grade (StudentID, Score) VALUES (2, 97);
INSERT INTO Grade (StudentID, Score) VALUES (2, 90);

INSERT INTO Grade (StudentID, Score) VALUES (3, 66);
INSERT INTO Grade (StudentID, Score) VALUES (3, 61);
INSERT INTO Grade (StudentID, Score) VALUES (3, 60);

有问题的查询:

SELECT Name,
    (SELECT AVG(Score) FROM Grade WHERE StudentID = Student.ID AND Score < 65) AS 'F',
    (SELECT AVG(Score) FROM Grade WHERE StudentID = Student.ID AND Score >= 65 AND Score < 70) AS 'D',
    (SELECT AVG(Score) FROM Grade WHERE StudentID = Student.ID AND Score >= 70 AND Score < 80) AS 'C',
    (SELECT AVG(Score) FROM Grade WHERE StudentID = Student.ID AND Score >= 80 AND Score < 90) AS 'B',
    (SELECT AVG(Score) FROM Grade WHERE StudentID = Student.ID AND Score >= 90 AND Score <= 100) AS 'A'
FROM Student

产生以下结果:

Name    F     D     C     B     A
-----------------------------------------
Steven  NULL  NULL  NULL  81    90
Timmy   63    NULL  NULL  NULL  95
Maria   60    66    NULL  NULL  NULL

我知道您可以使用 COUNT() 的技术。你在哪里执行一个 SELECT与 JOIN然后使用 CASE当主键在您的联接和您的条件为真之间排列时，可选择将 1 添加到计数器的语句。我正在寻找一种类似的技术，可以应用于不同类型的聚合(而不仅仅是 COUNT )。
有没有一种有效的方法可以将此示例查询转换为使用 JOIN而不是多个子查询？

Answer 1

也许我遗漏了一些东西，但使用 CASE 的解决方案也适用于聚合:

SELECT st.name, 
       avg(CASE WHEN g.score < 65 THEN g.score ELSE NULL END) as F,
       avg(CASE WHEN g.score >= 65 AND g.score < 70 THEN g.score ELSE NULL END) as D,
       avg(CASE WHEN g.score >= 70 AND g.score < 80 THEN g.score ELSE NULL END) as C,
       avg(CASE WHEN g.score >= 80 AND g.score < 90 THEN g.score ELSE NULL END) as B,
       avg(CASE WHEN g.score >= 90 AND g.score <= 100 THEN g.score ELSE NULL END) as A
FROM Grade g
  JOIN Student st ON g.studentid = st.ID
GROUP BY st.name