SQL-在WHERE子句中使用MAX [英] SQL - Using MAX in a WHERE clause

问题描述

假设值是一个int且以下查询有效:

Assume value is an int and the following query is valid:

SELECT blah
FROM table
WHERE attribute = value

尽管MAX(expression)返回int，但以下无效:

Though MAX(expression) returns int, the following is not valid:

SELECT blah
FROM table
WHERE attribute = MAX(expression)

当然，使用子查询可以达到预期的效果，但是我的问题是，为什么SQL是用这种方式设计的-是否有某种原因不允许这种事情发生?来自编程语言的学生总是可以通过返回该类型的函数调用来替换数据类型，这使这个问题令人困惑.有一种解释可以给他们，而不仅仅是说就是这样"吗?

OF course the desired effect can be achieved using a subquery, but my question is why was SQL designed this way - is there some reason why this sort of thing is not allowed? Students coming from programming languages where you can always replace a data-type by a function call that returns that type find this issue confusing. Is there an explanation one can give them rather than just saying "that's the way it is"?

推荐答案

这仅仅是因为查询的操作顺序.

It's just because of the order of operations of a query.

1. FROM子句
2. WHERE子句
3. GROUP BY子句
4. HAVING子句
5. SELECT子句
6. ORDER BY子句

WHERE仅过滤FROM返回的行.像MAX()这样的聚合函数无法返回结果，因为它甚至还没有应用于任何结果.

WHERE just filters the rows returned by FROM. An aggregate function like MAX() can't have a result returned because it hasn't even been applied to anything.

这也是原因，为什么您不能使用WHERE子句中SELECT子句中定义的别名，却可以使用FROM子句中定义的别名.

That's also the reason, why you can't use aliases defined in the SELECT clause in a WHERE clause, but you can use aliases defined in FROM clause.

这篇关于SQL-在WHERE子句中使用MAX的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！