下面是一個簡單的查詢，用于檢索學生及其考試結果。同一個學生可以多次參加同一個考試。子查詢檢索每個學生的最新考試成績。如您所見，X 行（檢索最新的考試 ID）在每一行的每個子查詢中都完全相同。如何存盤或快取 X 行的結果以防止每行執行 3 次？我不能為此任務使用存盤程序或函式，它必須是用于額外過濾的 VIEW。
SELECT S.*,
(
SELECT COUNT(*) FROM ExamAnswers WHERE
IsCorrectAnswer IS NOT NULL AND
IsCorrectAnswer = 1 AND
ExamID =
(SELECT TOP(1) ID FROM Exams E WHERE E.StudentID = S.ID ORDER BY ID DESC) --Line X
) CorrectAnswerCount,
(
SELECT COUNT(*) FROM ExamAnswers EA WHERE
EA.IsCorrectAnswer IS NOT NULL AND
EA.IsCorrectAnswer = 0 AND
EA.ExamID =
(SELECT TOP(1) ID FROM Exams E WHERE E.StudentID = S.ID ORDER BY ID DESC) --Line X
) WrongAnswerCount,
(
SELECT COUNT(*) FROM ExamAnswers WHERE
IsCorrectAnswer IS NULL AND
ExamID =
(SELECT TOP(1) ID FROM Exams E WHERE E.StudentID = S.ID ORDER BY ID DESC) --Line X
) UnansweredQuestionCount
FROM Students S
uj5u.com熱心網友回復：
你可以這樣做
SELECT S.*,
CA.*
FROM Students S
CROSS APPLY (SELECT SUM(CASE WHEN IsCorrectAnswer = 1 THEN 1 ELSE 0 END) AS CorrectAnswerCount,
SUM(CASE WHEN IsCorrectAnswer = 0 THEN 1 ELSE 0 END) AS WrongAnswerCount,
SUM(CASE WHEN IsCorrectAnswer IS NULL THEN 1 ELSE 0 END) AS UnansweredQuestionCount
FROM ExamAnswers EA
WHERE EA.ExamID = (SELECT TOP(1) ID
FROM Exams E
WHERE E.StudentID = S.ID
ORDER BY ID DESC)) CA
uj5u.com熱心網友回復：
這種方法怎么樣：
WITH
T AS
(
SELECT Student_id,
SUM(CASE IsCorrectAnswer WHEN 1 THEN 1 END) AS COUNT_TRUE,
SUM(CASE IsCorrectAnswer WHEN 0 THEN 1 END) AS COUNT_FALSE,
SUM(CASE WHEN IsCorrectAnswer IS NULL THEN 1 END) AS COUNT_UNKNOWN
FROM ExamAnswers AS EA
WHERE EA.ExamID = (SELECT MAX(ID)
FROM Exams E
WHERE E.StudentID = S.ID)
GROUP BY Student_id
)
SELECT S.*, COUNT_TRUE, COUNT_FALSE, COUNT_UNKNOWN
FROM Students AS S
JOIN T ON S.ID = T.Student_id
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/360897.html
標籤：sql-server 表现 查询语句 子查询