Sybase:HAVING 对行进行操作? [英] Sybase: HAVING operates on rows?

问题描述

我遇到了以下 SYBASE SQL:

I've came across the following SYBASE SQL:

-- Setup first
create table #t (id int, ts int)
go

insert into #t values (1, 2)
insert into #t values (1, 10)
insert into #t values (1, 20)
insert into #t values (1, 30)

insert into #t values (2, 5)
insert into #t values (2, 13) 
insert into #t values (2, 25)
go

declare @time int select @time=11
-- This is the SQL I am asking about
select * from (select * from #t where ts <= @time) t group by id having ts = max(ts)
go

这条SQL的结果是

 id          ts          
 ----------- ----------- 
           1          10 
           2           5 

这看起来像是应用于行而不是组的 HAVING 条件.有人可以指点我在 Sybase 15.5 文档中描述这种情况的地方吗?我所看到的只是HAVING 对组进行操作".我在文档中看到的最接近的是:

This looks like HAVING condition applied to rows rather than groups. Can someone please point me at a place is Sybase 15.5 documentation where this case is described? All I see is "HAVING operates on groups". The closest I see in the docs is:

having 子句可以包含不在其中的列或表达式选择列表而不是在 group by 子句中.

The having clause can include columns or expressions that are not in the select list and not in the group by clause.

(引用自 这里).

然而，他们并没有完全解释当你这样做时会发生什么.

However, they don't exactly explain what happens when you do that.

推荐答案

我的理解:是的，从根本上说，HAVING 对行进行操作.通过省略 GROUP BY，它对单个超组"内的所有结果行进行操作，而不是对组内的行进行操作.阅读您最初链接的 Sybase docco 中的分组依据和使用聚合查询如何工作"部分:-

My understanding: Yes, fundamentally, HAVING operates on rows. By omitting a GROUP BY, it operates on all result rows within a single "supergroup" rather than on rows-within-groups. Read the section "How group by and having queries with aggregates work" in your originally-linked Sybase docco:-

分组依据和使用聚合进行查询的工作原理

• where 子句排除不符合其搜索条件的行；对于分组或非分组查询，其功能保持不变.
• group by 子句将 group by 表达式中每个唯一值的剩余行收集到一个组中.省略 group by 会为整个表创建一个组.
• 选择列表中指定的聚合函数计算每个组的汇总值.对于标量聚合，表只有一个值.向量聚合计算不同组的值.
• have 子句从结果中排除不符合其搜索条件的组.即使 have 子句只测试行，group by 子句的存在或不存在可能使它看起来是在对组进行操作:
  • 当查询包括分组依据时，具有排除结果组行.这就是为什么拥有似乎对群体起作用.
  • 当查询没有分组依据时，具有会从(单组)表中排除结果行.这就是为什么 have 似乎对行进行操作(结果类似于 where 子句结果).
  • The where clause excludes rows that do not meet its search conditions; its function remains the same for grouped or nongrouped queries.
  • The group by clause collects the remaining rows into one group for each unique value in the group by expression. Omitting group by creates a single group for the whole table.
  • Aggregate functions specified in the select list calculate summary values for each group. For scalar aggregates, there is only one value for the table. Vector aggregates calculate values for the distinct groups.
  • The having clause excludes groups from the results that do not meet its search conditions. Even though the having clause tests only rows, the presence or absence of a group by clause may make it appear to be operating on groups:
    • When the query includes group by, having excludes result group rows. This is why having seems to operate on groups.
    • When the query has no group by, having excludes result rows from the (single-group) table. This is why having seems to operate on rows (the results are similar to where clause results).

    其次，"being、group by 和where 子句如何交互":-

    have、group by 和 where 子句如何相互作用

    当您在查询中包含 have、group by 和 where 子句时，每个子句影响行的顺序决定了最终结果:

    When you include the having, group by, and where clauses in a query, the sequence in which each clause affects the rows determines the final results:

    • where 子句排除不符合其搜索条件的行.
    • group by 子句将 group by 表达式中每个唯一值的剩余行收集到一个组中.
    • 选择列表中指定的聚合函数计算每个组的汇总值.
    • have 子句从最终结果中排除不符合其搜索条件的行.
    • The where clause excludes rows that do not meet its search conditions.
    • The group by clause collects the remaining rows into one group for each unique value in the group by expression.
    • Aggregate functions specified in the select list calculate summary values for each group.
    • The having clause excludes rows from the final results that do not meet its search conditions.

    @SQLGuru 的解释说明了这一点.

    @SQLGuru's explanation is an illustration of this.

    在相关方面，我对使用 TSQL扩展列"的不符合 ANSI 的查询的行为感到惊讶.Sybase 通过创建与原始表和 (iii) WHERE 子句的额外连接来处理 WHERE 子句 (ii) 之后的扩展列连接中不使用子句.此类查询可能返回比预期更多的行，然后 HAVING 子句需要额外的条件来过滤掉这些行.

    On a related point, I was surprised by the behaviour of non-ANSI-conforming queries that utilise TSQL "extended columns". Sybase handles the extended columns (i) after the WHERE clause (ii) by creating extra joins to the original tables and (iii) the WHERE clause is not used in the join. Such queries might return more rows than expected and the HAVING clause then requires additional conditions to filter these out.

    查看示例 b、c 和 d 在您最初链接的 docco 页面上的 Transact-SQL 扩展以分组依据和具有" 下.我发现安装来自 Sybase 的 pubs2 示例数据库与示例一起玩很有用.

    See examples b, c and d under "Transact-SQL extensions to group by and having" on the page of your originally-linked docco. I found it useful to install the pubs2 sample database from Sybase to play along with the examples.

    这篇关于Sybase:HAVING 对行进行操作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！