如何选择postgresql中多列值多次出现的所有行

Question

如何选择postgresql中多列值多次出现的所有行？我想查找具有相同product_id和entry_date的行。

Input:
id     product_id  entry_date                   product_name stock
1      009         2020-12-11 02:05:20.09876    apple        5
2      001         2020-12-11 03:04:10.09876    orange       9
3      004         2020-12-11 10:05:20.09876    pineapple    4
4      002         2020-12-11 02:05:20.09876    berry        5
5      009         2020-12-11 02:05:20.09876    apple        2
6      004         2020-12-11 10:05:20.09876    pineapple    1
7      006         2020-12-11 10:05:20.09876    pineapple    4
select * from 
( select *, 
count(1) over (partition by entry_date and product_id ) as occurs
from sale_order where entry_date::text LIKE '2020-12-11%'
) AS t where occurs > 1 ;
Output:
id     product_id  entry_date                   product_name stock
1      009         2020-12-11 02:05:20.09876    apple        5
3      004         2020-12-11 10:05:20.09876    pineapple    4
5      009         2020-12-11 02:05:20.09876    apple        2
6      004         2020-12-11 10:05:20.09876    pineapple    1

Answer 1

使用窗口函数-count如下

select * from
(select t.*,
count(1) over (partition by product_id, entry_Date) as cnt
from t) t
where cnt > 1

您也可以按如下方式使用exists：

select * from t
where exists
(select 1 from t tt
where t.product_id = tt.product_id
and t.entry_Date = tt.entry_date
and t.id <> tt.id)

Answer 2

您使用COUNT()窗口函数的方法非常正确-只需稍微更改语法(用,替换and(

演示：db<gt；小提琴

SELECT 
*
FROM (
SELECT
*,
count(1) OVER (PARTITION BY entry_date, product_id)
FROM
t
) s
WHERE count > 1 

另外：

您可以简单地将时间戳转换为日期，而不是使用字符串操作来过滤您的一天：

WHERE entry_date::date = '2020-12-11'