HAVING子句如何真正起作用？

We able to use HAVING clause in SQL-query to filtrate groups of row. When we use GROUP BY clause it work directly in this way.

我们能够在SQL查询中使用HAVING子句来过滤行组。当我们使用GROUP BY子句时，它以这种方式直接工作。

But, let's look to this query:

但是，让我们看看这个查询：

select 1 where 1!=1 having count(*)=0;

(or append it with 'from dual' for Oracle).

（或者在Oracle中加上'from dual'）。

If HAVING really do group filtration, after WHERE we have no any rows, so we have no any group and result must be 'No row selected'.

如果HAVING真的进行了组过滤，在WHERE之后我们没有任何行，所以我们没有任何组，结果必须是'No row selected'。

But in PostgreSQL, MySQL and Oracle we get '1' as result of query.

但是在PostgreSQL，MySQL和Oracle中，我们得到'1'作为查询结果。

Question: how does HAVING really work?

问题：HAVING如何真正起作用？

SQL Fiddle for test: http://www.sqlfiddle.com/#!15/d5407/51

SQL小提琴测试：http：//www.sqlfiddle.com/#！15 / d5407 / 51

2 个解决方案

#1

If there's no GROUP BY an aggregate always returns a row, in your case the COUNT(*) returns 0.

如果没有GROUP BY，则聚合总是返回一行，在您的情况下，COUNT（*）返回0。

This column is not in your Select list, but the hard-coded literal 1

此列不在您的选择列表中，而是硬编码的文字1

select count(*) where 1!=1 ;
select 'bla' where 1!=1 having count(*)=0;

See fiddle

看小提琴

#2

HAVING without GROUP BY cluase is valid and operates on entire table. From SQL Standard 92:

没有GROUP BY cluase的HAVING是有效的，并且可以在整个表格上运行。从SQL标准92：

7.10

7.10

::= HAVING

:: = HAVING

Syntax Rules

语法规则

1) Let HC be the . Let TE be the that immediately contains HC.

1）让HC成为。设TE是立即包含HC的。

If TE does not immediately contain a , then GROUP BY ( ) is implicit.

如果TE不立即包含a，那么GROUP BY（）是隐式的。

and:

和：

::= GROUP BY

:: = GROUP BY

<grouping specification> ::=
<grouping column reference>
     | <rollup list>   
     | <cube list>   
     | <grouping sets list>   
     | <grand total>   
     | <concatenated grouping>

<grouping set> ::=
<ordinary grouping set>   
     | <rollup list>   
     | <cube list>   
     | <grand total>

<grand total> ::= <left paren> <right paren>

As you see GROUP BY () is treated as grand total.

如您所见，GROUP BY（）被视为总计。

In your example you have:

在您的示例中，您有：

select 1 
where 1!=1 
having count(*)=0;

is actually something like:

实际上是这样的：

select 1 
where 1!=1 
-- group by ()
having count(*)=0;

#1