postgresql如何查询重复计数及去重查询

postgresql查询重复计数及去重查询

1、重复计数

SELECT id, name, email,
count(*) over (partition by email) cnt
FROM people

2、字段相同值计数排序

SELECT id, name, email,
row_number() over (PARTITION BY email ORDER BY id DESC) AS row_num
FROM people

3、去重查询

3.1、

select distinct on (a) a,b,c from t_distinct;

返回a唯一的任意行

3.2、

select distinct a,b,c from t_distinct;

返回 a,b,c 唯一值。

postgresql对字段中的重复值计数

1、重复计数

SELECT 号码,属性,count(*) over (PARTITION BY 号码) AS “row_num”
from (select 号码,‘需更换终端用户' as 属性 from “市场部0330需更换终端用户明细” union
SELECT 号码,‘需更换卡槽' as 属性 FROM “市场部0330需升级终端版本打开VoLTE开关更换卡槽”) as ss

2、字段相同值计数排序

SELECT 号码,属性,row_number() over (PARTITION BY 号码 ORDER BY 号码 DESC) AS “row_num”
from (select 号码,‘需更换终端用户' as 属性 from “市场部0330需更换终端用户明细” union
SELECT 号码,‘需更换卡槽' as 属性 FROM “市场部0330需升级终端版本打开VoLTE开关更换卡槽”) as ss

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。