如何使用 sql 语句识别唯一和重复记录

Question

我有一个在特定列中有多个值的表，我正在尝试编写两个sql查询，它将识别整个重复项集和整组唯一记录，我写了很少的查询，但在唯一集中，我从重复集中获取一条记录。

样本数据，

pay_id, pay_ratio, pay_type, cor_id
123,    12,        C,        Annual
123,    12,        C,        Annual
456,    13,        A,        Semi-Annual
476,    43,        B,        Monthly
987,    32,        H,        Daily
987,    32,        H,        Daily

我正在尝试将上述数据集分开，如下所示。

唯一数据集

pay_id, pay_ratio, pay_type, cor_id
456,    13,        A,        Semi-Annual
476,    43,        B,        Monthly

重复数据集

pay_id, pay_ratio, pay_type, cor_id
123,    12,        C,        Annual
123,    12,        C,        Annual
987,    32,        H,        Daily
987,    32,        H,        Daily

有人可以建议我如何使用SQL查询来实现这一点。

问候 西

Answer 1

您可以使用COUNT() OVER()执行此操作：

SELECT pay_id, pay_ratio, pay_type, cor_id,
CASE 
WHEN COUNT(*) OVER (PARTITION BY pay_id, pay_ratio, pay_type, cor_id) = 1 
THEN 'unique'
ELSE 'dupl'
END AS type
FROM mytable

上述查询返回唯一记录的'unique'和重复记录的'dupl'。您可以将查询包装在CTE或子查询中，并根据需要对其进行筛选。

注意：上述查询基于表的所有 4 个字段确定重复记录的假设。您可以根据需要更改PARTITION BY子句，以解决其他一些重复的"逻辑"。

Answer 2

使用带有row_number()的公用表表达式

独特：

;with cte as (
select *
, rn = row_number() over (partition by pay_id order by pay_type)
from t
)
select *
from cte
where not exists (
select 1
from cte i
where i.pay_id = cte.pay_id
and i.rn > 1
)

Rextester 演示：http://rextester.com/LHIGTY81886

返回：

+--------+-----------+----------+-------------+----+
| pay_id | pay_ratio | pay_type |   cor_id    | rn |
+--------+-----------+----------+-------------+----+
|    456 |        13 | A        | Semi-Annual |  1 |
|    476 |        43 | B        | Monthly     |  1 |
+--------+-----------+----------+-------------+----+

重复：

;with cte as (
select *
, rn = row_number() over (partition by pay_id order by pay_type)
from t
)
select *
from cte
where exists (
select 1
from cte i
where i.pay_id = cte.pay_id
and i.rn > 1
)

返回：

+--------+-----------+----------+--------+----+
| pay_id | pay_ratio | pay_type | cor_id | rn |
+--------+-----------+----------+--------+----+
|    123 |        12 | C        | Annual |  1 |
|    123 |        12 | C        | Annual |  2 |
|    987 |        32 | H        | Daily  |  1 |
|    987 |        32 | H        | Daily  |  2 |
+--------+-----------+----------+--------+----+

Answer 3

分组和计数：

SELECT
pay_id, pay_ratio, pay_type, cor_id,
COUNT(*) AS [Duplicates]
FROM table
GROUP BY pay_id, pay_ratio, pay_type, cor_id
HAVING COUNT(*) = 0 /* unique; > 0 - duplicates */
ORDER BY COUNT(*) ASC