主页 / dba / 问题 / 201538
Accepted
user1451111
Asked: 2018-03-18 06:11:00 +0800 CST

将非相关子查询转换为连接语句

  • 772

是否可以摆脱子查询并将整个查询转换为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;

并得到这个错误：

列 'c.max_allowed' 在 HAVING 子句中无效，因为它未包含在聚合函数或 GROUP BY 子句中。

我想这样做是为了在有数千条记录的情况下可能提高查询速度。我的担心是否有效？

环境： SQL Server 2000

sql-server performance

1 个回答

  1. Best Answer

    您的初始查询显示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;

    但我看不出这样做有什么好处。原始查询更清晰并且可能同样或更有效。

    我看到的执行计划是

    在此处输入图像描述

    select againstconfigurations执行一次 - 然后查询的其余部分作为独立的子树运行，最终消除HAVING与INNER JOIN.

    要优化此查询，您应该考虑在 上创建索引attendance, student_id。attendance = 'ABSENT'这将允许有效地识别与谓词匹配的行。它们将被排序，因此无需排序即可由流聚合处理。

    另一种选择是使用索引视图预先计算聚合。

    • 3

相关问题