HAVING子句的意外行为

Question

我在MySQL中有以下表格-

runs overs
 5     6
 6     7

我正在尝试测试没有group by子句的having子句的行为。

When i write-
select * from cricket having runs=MIN(runs);
OUTPUT-
runs overs
 5     6

select * from cricket having runs=MAX(runs);
OUTPUT-
Empty Set

这种行为背后的原因是什么？

EDIT:在反转行顺序时-

runs overs
 6     7
 5     6

输出结果如下：

select * from cricket having runs=MIN(runs);
OUTPUT-
Empty Set

select * from cricket having runs=MAX(runs);
OUTPUT-
runs overs
 6     7

Answer 1

您使用select、聚合函数和having子句的方式不正确..

您是否正在使用mysql 5.7之前的版本，因此该行为是允许的，但具有不可预测的结果。(对于mysql版本高于5.6的查询，默认这种查询会产生错误)

使用aggreagtion函数的准备方法是基于

在group by子句中提及聚合函数中未涉及的列

所以你的第一个查询应该是

select * 
    from cricket 
    having runs = ( 
        select min(runs) from cricket
    )

或

select c.* 
from cricket c
inner join  (
    select overs, min(runs) min_runs
    from cricket 
    group by  overs
    order by min_runs desc limit 1
) t on t.overs = c.overs 
    and t.min_runs = c.runs