我遇到了这个难题
in the comments here
CREATE TABLE r (b INT);
SELECT 1 FROM r HAVING 1=1;
SQL Server和PostgreSQL返回1行.
MySQL和Oracle返回零行.
哪个是对的？或两者同样有效？
解决方法
按标准：
SELECT 1 FROM r HAVING 1=1
手段
SELECT 1 FROM r GROUP BY () HAVING 1=1
引用ISO / IEC 9075-2：2011 7.10语法规则1(HAVING子句定义的一部分)：
Let HC be the <having clause>. Let TE be the <table expression> that
immediately contains HC. If TE does not immediately contain a
<group by clause>,then “GROUP BY ()” is implicit. Let T be the
descriptor of the table defined by the <group by clause> GBC immediately
contained in TE and let R be the result of GBC.
好的,非常清楚.
断言：1 = 1是真正的搜索条件.我不会为此提供任何引用.
现在
SELECT 1 FROM r GROUP BY () HAVING 1=1
是等于
SELECT 1 FROM r GROUP BY ()
引用ISO / IEC 9075-2：2011 7.10一般规则1：
The <search condition> is evaluated for each group
of R. The result of the <having clause> is a grouped
table of those groups of R for which the result of the
<search condition> is True.
逻辑：由于搜索条件始终为真,因此结果为R,这是group by expression的结果.
以下摘录自7.9的一般规则(GROUP BY CLAUSE的定义)
1) If no <where clause> is specified,then let T be the result of the preceding <from clause>; otherwise,let T be the result of the preceding <where clause>.
2) Case:
a) If there are no grouping columns,then the result of
the <group by clause> is the grouped table consisting of T as its only group.
因此,我们可以得出结论
FROM r GROUP BY ()
得到一个分组表,由一个组组成,零行(因为R为空).
7.12的一般规则的摘录,它定义了一个查询规范(a.k.a一个SELECT语句)：
1) Case:
a) If T is not a grouped table,then […]
b) If T is a grouped table,then
Case:
i) If T has 0 (zero) groups,then let TEMP be an empty table.
ii) If T has one or more groups,then each <value expression> is applied
to each group of T yielding a table TEMP of M rows,where M is the
number of groups in T. The i-th column of TEMP contains the values
derived by the evaluation of the i-th <value expression>. […]
2) Case:
a) If the <set quantifier> DISTINCT is not specified,then the
result of the <query specification> is TEMP.
因此,由于表有一个组,因此它必须有一个结果行.
从而
SELECT 1 FROM r HAVING 1=1
应返回1行结果集.
证明完毕 （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
