如何在 Oracle 中的表中查找重复值?

2021-12-05 00:00:00 sql oracle duplicate-data

返回给定列的重复值及其在 Oracle 数据库表中出现次数的最简单的 SQL 语句是什么?

What's the simplest SQL statement that will return the duplicate values for a given column and the count of their occurrences in an Oracle database table?

例如:我有一个 JOBS 表,其中包含 JOB_NUMBER 列.我如何知道我是否有任何重复的 JOB_NUMBER,以及它们被复制了多少次?

For example: I have a JOBS table with the column JOB_NUMBER. How can I find out if I have any duplicate JOB_NUMBERs, and how many times they're duplicated?

推荐答案

按 COUNT 聚合列,然后使用 HAVING 子句查找出现次数大于一次的值.

Aggregate the column by COUNT, then use a HAVING clause to find values that appear greater than one time.

SELECT column_name, COUNT(column_name)
FROM table_name
GROUP BY column_name
HAVING COUNT(column_name) > 1;

相关文章