Postgres：选择字段数大于1的所有行 [英] Postgres: select all row with count of a field greater than 1

问题描述

我有一个存储产品价格信息的表，该表看起来类似于（否是主键）

i have table storing product price information, the table looks similar to, (no is the primary key)

no   name    price    date
1    paper   1.99     3-23
2    paper   2.99     5-25
3    paper   1.99     5-29
4    orange  4.56     4-23
5    apple   3.43     3-11

现在我要选择名称字段出现多次的所有行在桌子上。基本上，我希望查询返回前三行。

right now I want to select all the rows where the "name" field appeared more than once in the table. Basically, i want my query to return the first three rows.

我尝试过：

SELECT * FROM product_price_info GROUP BY name HAVING COUNT(*) > 1  

但我收到一条错误消息：

but i get an error saying:

列 product_price_info.no​​必须出现在GROUP BY子句中或在聚合函数中使用

column "product_price_info.no" must appear in the GROUP BY clause or be used in an aggregate function

推荐答案

SELECT * 
FROM product_price_info 
WHERE name IN (SELECT name 
               FROM product_price_info 
               GROUP BY name HAVING COUNT(*) > 1)

这篇关于Postgres：选择字段数大于1的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！