postgres中的distinct（）函数（不选择限定符） [英] distinct() function (not select qualifier) in postgres

问题描述

我刚遇到一个SQL查询，特别是针对使用名为 distinct的函数的Postgres数据库。即：

I just came across a SQL query, specifically against a Postgres database, that uses a function named "distinct". Namely:

select distinct(pattern) as pattern, style, ... etc ...
from styleview
where ... etc ...

请注意，这不是普通的DISTINCT限定词SELECT-至少不是DISTINCT限定词的常规语法，请注意括号。显然是在使用DISTINCT作为函数，或者也许这是一些特殊的语法。

Note this is NOT the ordinary DISTINCT qualifier on a SELECT -- at least it's not the normal syntax for the DISTINCT qualifier, note the parentheses. It is apparently using DISTINCT as a function, or maybe this is some special syntax.

任何人都知道这意味着什么吗？

Any idea what this means?

我尝试了一下，如果我写了

I tried playing with it a little and if I write

select distinct(foo)
from bar

我得到的结果与

select distinct foo
from bar

当我将其组合时与同一选择中的其他字段一样，我不清楚它在做什么。

When I combine it with other fields in the same select, it's not clear to me exactly what it's doing.

我在Postgres文档中找不到任何内容。

I can't find anything in the Postgres documentation.

谢谢您的帮助！

推荐答案

^{（这个问题很旧，但在Google的 SQL区别不是函数结果中排名第二（堆栈溢出第一，第二个），但仍然缺少令人满意的答案，所以...）}

实际上，这个是 SELECT上的普通DISTINCT限定词-但语法具有误导性（您是rig ht）。

Actually this is the ordinary DISTINCT qualifier on a SELECT -- but with a misleading syntax (you are right about that point).

DISTINCT从来不是函数，而总是关键字。在这里（错误地）使用它就像是一个函数，但是

DISTINCT is never a function, always a keyword. Here it is used (wrongly) as if it were a function, but

select distinct(pattern) as pattern, style, ... etc ...
from styleview
where ... etc ...

实际上等效于以下所有形式：

is in fact equivalent to all the following forms:

_{-在与众不同的地方：}

select distinct (pattern) as pattern, style, ... etc ...
from styleview
where ... etc ...

_{-删除列名周围的括号：}

select distinct pattern as pattern, style, ... etc ...
from styleview
where ... etc ...

_{-缩进子句内容：<​​/ em>}

select distinct
    pattern as pattern, style, ... etc ...
from
    styleview
where
    ... etc ...

_{-删除冗余别名列名称的缩写：}

select distinct
    pattern, style, ... etc ...
from
    styleview
where
    ... etc ...

补充阅读：

• http://weblogs.sqlteam.com/jeffs/archive/2007/10/12/sql-distinct-group -by.aspx


• https://stackoverflow.com/a/1164529

• http://weblogs.sqlteam.com/jeffs/archive/2007/10/12/sql-distinct-group-by.aspx
• https://stackoverflow.com/a/1164529

注意：当前问题的答案提到了 DISTINCT ON PostgreSQL扩展的功能。

但是（正如Jay在评论中正确指出的那样），这里并没有使用它，因为查询（和结果）应该是不同的，例如：

Note: OMG Ponies in an answer to the present question mentioned the DISTINCT ON extension featured by PostgreSQL.
But (as Jay rightly remarked in a comment) it is not what is used here, because the query (and the results) would have been different, e.g.:

select distinct on(pattern) pattern, style, ... etc ...
from styleview
where ... etc ...
order by pattern, ... etc ...

等效于：

select  distinct on (pattern)
    pattern, style, ... etc ...
from
    styleview
where
    ... etc ...
order by
    pattern, ... etc ...

互补阅读：

• http://www.noelherrick.com/blog/postgres-distinct-on

• http://www.noelherrick.com/blog/postgres-distinct-on

注意：Lukas Eder在当前问题的答案提到了在聚合函数中使用DISTINCT关键字的语法：

< c $ c> COUNT（DISTINCT（foo，bar，...））语法b y HSQLDB

（或 COUNT（DISTINCT foo，bar，...）也适用于MySQL，也适用于PostgreSQL，SQL Server， Oracle，也许还有其他人。）

但是（显然）这不是这里使用的。

Note: Lukas Eder in an answer to the present question mentioned the syntax of using the DISTINCT keyword inside an aggregate function:
the COUNT(DISTINCT (foo, bar, ...)) syntax featured by HSQLDB
(or COUNT(DISTINCT foo, bar, ...) which works for MySQL too, but also for PostgreSQL, SQL Server, Oracle, and maybe others).
But (clearly enough) it is not what is used here.

这篇关于postgres中的distinct（）函数（不选择限定符）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！