PostgreSQL等价于MySQL群

Question

我需要在桌子上找到复制件。在MySQL中，我只需写：

SELECT *,count(id) count FROM `MY_TABLE`
GROUP BY SOME_COLUMN ORDER BY count DESC

这个查询很好：

• 基于SOME_COLUMN查找重复项，并给出其重复计数。
• 排序按顺序重复，这是有用的快速扫描主要的欺骗。
• 为所有剩余的列选择一个随机值，使我对这些列中的值有一个概念。

Postgres中的类似查询向我问好时出错：

列"MY_TABLE.SOME_COLUMN“必须出现在GROUP子句中，或用于聚合函数中。

与此查询等效的Postgres是什么？

PS:我知道MySQL行为偏离了SQL标准。

Answer 1

倒计时是一个非标准的MySQL的东西。使用标准双引号来引用标识符(也可以在MySQL中使用)。也就是说，如果您的表实际上名为"MY_TABLE" (都是大写)。如果您(更明智地)将其命名为my_table (所有小写)，则可以删除双引号或使用小写。

另外，我使用ct而不是count作为别名，因为使用函数名作为标识符是错误的做法。

简单案例

这将适用于PostgreSQL 9.1

SELECT *, count(id) ct
FROM   my_table
GROUP  BY primary_key_column(s)
ORDER  BY ct DESC;

它需要GROUP BY子句中的主键列。结果是与查询相同的，但是ct总是1(如果id IS NULL是0)--找重复的没有用。

按主键列以外的其他列分组

如果您想按其他列进行分组，事情就会变得更加复杂。此查询模拟MySQL查询的行为，可以使用*。

SELECT DISTINCT ON (1, some_column)
       count(*) OVER (PARTITION BY some_column) AS ct
      ,*
FROM   my_table
ORDER  BY 1 DESC, some_column, id, col1;

这是因为DISTINCT ON (PostgreSQL专用的)，比如DISTINCT (Standard)，是在窗口函数count(*) OVER (...)之后应用的。窗口函数 (带有OVER子句)需要PostgreSQL 8.4或更高版本，并且在MySQL中不可用。

与任何表一起工作，而不考虑主约束或唯一约束。

1 in DISTINCT ON和ORDER BY仅仅是指SELECT列表中项目的序号。

SQL Fiddle并排演示两种方法。

在这个密切相关的答案中有更多的细节：

• 在每一组中选择第一行？

count(*)对count(id)

如果您正在寻找副本，那么使用count(*)比使用count(id)更好。如果id可以是NULL，则有一个微妙的区别，因为NULL值不被计算，而count(*)则计算所有行。如果id被定义为NOT NULL，则结果是相同的，但是count(*)通常更合适(也更快)。

Answer 2

下面是另一种方法，在以下方面使用不同的方法：

select 

  distinct on(ct, some_column) 

  *,
  count(id) over(PARTITION BY some_column) as ct

from my_table x
order by ct desc, some_column, id

数据来源：

CREATE TABLE my_table (some_column int, id int, col1 int);

INSERT INTO my_table  VALUES
 (1, 3,  4)
,(2, 4,  1)
,(2, 5,  1)
,(3, 6,  4)
,(3, 7,  3)
,(4, 8,  3)
,(4, 9,  4)
,(5, 10, 1)
,(5, 11, 2)
,(5, 11, 3);

输出：

SOME_COLUMN ID          COL1        CT
5           10          1           3
2           4           1           2
3           6           4           2
4           8           3           2
1           3           4           1

现场测试：http://www.sqlfiddle.com/#!1/e2509/1

与文档不同：http://www.postgresonline.com/journal/archives/4-Using-Distinct-ON-to-return-newest-order-for-each-customer.html

Answer 3

mysql允许group by从group by列表中省略非聚合的选定列，它通过返回每个按列分组的唯一组合的第一行来执行。这是非标准的SQL行为。

相反，postgres是符合SQL标准的。

在postgres中没有类似的查询。