SQL - 拥有与在哪里 [英] SQL - HAVING vs. WHERE
问题描述
我有以下两张表:
1. Lecturers (LectID, Fname, Lname, degree).
2. Lecturers_Specialization (LectID, Expertise).
我想找到最专业的讲师.当我尝试这个时,它不起作用:
I want to find the lecturer with the most Specialization. When I try this, it is not working:
SELECT
L.LectID,
Fname,
Lname
FROM Lecturers L,
Lecturers_Specialization S
WHERE L.LectID = S.LectID
AND COUNT(S.Expertise) >= ALL (SELECT
COUNT(Expertise)
FROM Lecturers_Specialization
GROUP BY LectID);
但是当我尝试这个时,它可以工作:
But when I try this, it works:
SELECT
L.LectID,
Fname,
Lname
FROM Lecturers L,
Lecturers_Specialization S
WHERE L.LectID = S.LectID
GROUP BY L.LectID,
Fname,
Lname
HAVING COUNT(S.Expertise) >= ALL (SELECT
COUNT(Expertise)
FROM Lecturers_Specialization
GROUP BY LectID);
是什么原因?谢谢.
推荐答案
WHERE
子句在 individual rows 上引入条件;HAVING
子句在聚合 上引入了一个条件,即从 产生单个结果(例如计数、平均值、最小值、最大值或总和)的选择结果>多个行.您的查询需要第二种条件(即聚合条件),因此 HAVING
可以正常工作.
WHERE
clause introduces a condition on individual rows; HAVING
clause introduces a condition on aggregations, i.e. results of selection where a single result, such as count, average, min, max, or sum, has been produced from multiple rows. Your query calls for a second kind of condition (i.e. a condition on an aggregation) hence HAVING
works correctly.
根据经验,在 GROUP BY
之前使用 WHERE
,在 GROUP BY
之后使用 HAVING
.这是一个相当原始的规则,但在超过 90% 的情况下它是有用的.
As a rule of thumb, use WHERE
before GROUP BY
and HAVING
after GROUP BY
. It is a rather primitive rule, but it is useful in more than 90% of the cases.
当您使用它时,您可能希望使用 ANSI 版本的连接重写您的查询:
While you're at it, you may want to re-write your query using ANSI version of the join:
SELECT L.LectID, Fname, Lname
FROM Lecturers L
JOIN Lecturers_Specialization S ON L.LectID=S.LectID
GROUP BY L.LectID, Fname, Lname
HAVING COUNT(S.Expertise)>=ALL
(SELECT COUNT(Expertise) FROM Lecturers_Specialization GROUP BY LectID)
这将消除用作 theta 连接条件的 WHERE
.
这篇关于SQL - 拥有与在哪里的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!