SQL Postgres：选择字段计数大于1的所有行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36358833/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres: select all row with count of a field greater than 1
提问by user2628641
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.
现在我想选择“名称”字段在表中多次出现的所有行。基本上，我希望我的查询返回前三行。
I tried:
我试过：
SELECT * FROM product_price_info GROUP BY name HAVING COUNT(*) > 1
but i get an error saying:
但我收到一条错误消息：
column "product_price_info.no" must appear in the GROUP BY clause or be used in an aggregate function
列“product_price_info.no”必须出现在 GROUP BY 子句中或用于聚合函数中
回答by Juan Carlos Oropeza
SELECT *
FROM product_price_info
WHERE name IN (SELECT name
FROM product_price_info
GROUP BY name HAVING COUNT(*) > 1)
回答by Giorgos Betsos
Try this:
尝试这个：
SELECT no, name, price, "date"
FROM (
SELECT no, name, price, "date",
COUNT(*) OVER (PARTITION BY name) AS cnt
FROM product_price_info ) AS t
WHERE t.cnt > 1
You can use the window version of COUNTto get the population of each namepartition. Then, in an outer query, filter out namepartitions having a population that is less than 2.
您可以使用 的窗口版本COUNT来获取每个name分区的人口。然后，在外部查询中，过滤掉name人口少于 2 的分区。
回答by Jeff C Johnson
Window Functionsare really nice for this.
窗口函数对此非常有用。
SELECT p.*, count(*) OVER (PARTITION BY name) FROM product p;
For a full example:
完整示例：
CREATE TABLE product (no SERIAL, name text, price NUMERIC(8,2), date DATE);
INSERT INTO product(name, price, date) values
('paper', 1.99, '2017-03-23'),
('paper', 2.99, '2017-05-25'),
('paper', 1.99, '2017-05-29'),
('orange', 4.56, '2017-04-23'),
('apple', 3.43, '2017-03-11')
;
WITH report AS (
SELECT p.*, count(*) OVER (PARTITION BY name) as count FROM product p
)
SELECT * FROM report WHERE count > 1;
Gives:
给出：
no | name | price | date | count
----+--------+-------+------------+-------
1 | paper | 1.99 | 2017-03-23 | 3
2 | paper | 2.99 | 2017-05-25 | 3
3 | paper | 1.99 | 2017-05-29 | 3
(3 rows)
回答by jarlh
Self join version, use a sub-query that returns the name's that appears more than once.
自连接版本，使用返回出现多次的名称的子查询。
select t1.*
from tablename t1
join (select name from tablename group by name having count(*) > 1) t2
on t1.name = t2.name
Basically the same as IN/EXISTSversions, but probably a bit faster.
与IN/EXISTS版本基本相同，但可能要快一些。
