將非相關子查詢轉換為連接語句

是否可以擺脫子查詢並將整個查詢轉換為JOIN查詢？

SELECT student_id,
      COUNT(attendance)
FROM   student_attendance
WHERE  attendance = 'ABSENT'
GROUP  BY student_id
HAVING COUNT(attendance) = (SELECT max_allowed
                           FROM   configurations); 

我嘗試了以下方法：

SELECT student_id,
      COUNT(attendance)
FROM   student_attendance,
      configurations c
WHERE  attendance = 'ABSENT'
GROUP  BY student_id
HAVING COUNT(attendance) = c.max_allowed; 

並得到這個錯誤：

HAVING 子句中的“c.max_allowed”列無效，因為它既不包含在聚合函式中，也不包含在 GROUP BY 子句中。

我想這樣做是為了在數千條記錄的情況下提高查詢速度。我的擔心是否有效？

環境： SQL Server 2000

您的初始查詢顯示configurations必須不超過一行（否則會失敗）。您可以使用以下方式將其重寫為連接

SELECT student_id,
      COUNT(attendance)
FROM   student_attendance
      CROSS JOIN configurations c
WHERE  attendance = 'ABSENT'
GROUP  BY student_id
HAVING COUNT(attendance) = MAX(c.max_allowed); 

或者作為

SELECT student_id,
      COUNT(attendance)
FROM   student_attendance CROSS JOIN
      configurations c
WHERE  attendance = 'ABSENT'
GROUP  BY student_id, c.max_allowed
HAVING COUNT(attendance) = c.max_allowed; 

或者作為

SELECT t.*
FROM   (SELECT student_id,
              COUNT(attendance) AS count_attendance
       FROM   student_attendance
       WHERE  attendance = 'ABSENT'
       GROUP  BY student_id) AS t
      JOIN configuration AS c
        ON t.count_attendance = c.max_allowed; 

但我看不出這樣做有什麼好處。原始查詢更清晰，並且可能同樣或更有效。

我看到的執行計劃是

選擇反對configurations執行一次 - 然後查詢的其餘部分作為獨立的子樹執行，最終消除HAVING與INNER JOIN.

要優化此查詢，您應該考慮在attendance, student_id. attendance = 'ABSENT'這將允許有效地辨識與謂詞匹配的行。並且它們將被排序，因此可以由流聚合處理而無需排序。

另一種選擇是使用索引視圖預先計算聚合。

引用自：https://dba.stackexchange.com/questions/201538