查看 oracle 中重复行的所有数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8718458/
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
view all data for duplicate rows in oracle
提问by Marc
I've got a table with 6 columns:
我有一个有 6 列的表：
idnametype_idcodelatlong
idnametype_idcodelatlong
The first three are required. IDis the private key, inserted automatically with a sequence.
前三个是必须的。 ID是私钥，自动插入序列。
I have some rows that are duplicates, as defined by BOTH the nameand type_idbeing equal, but i'd like to view all the data for the dupes. I can find the dupes simply enough:
我有一些重复的行，由 BOTH thename和equals 定义type_id，但我想查看欺骗者的所有数据。我可以很简单地找到这些骗子：
SELECT name
, type_id
FROM table1
GROUP BY name
, type_id
HAVING COUNT(*) > 1
but actually viewing all the info is confounding me. I know this should be simple, but I'm hitting a wall here.
但实际上查看所有信息让我感到困惑。我知道这应该很简单，但我在这里碰壁了。
回答by Justin Cave
You can always use the GROUP BY/ HAVINGquery in an IN clause. This works and is relatively straightforward but it may not be particularly efficient if the number of duplicate rows is relatively large.
您始终可以在 IN 子句中使用GROUP BY/HAVING查询。这有效并且相对简单，但如果重复行的数量相对较大，它可能不是特别有效。
SELECT *
FROM table1
WHERE (name, type_id) IN (SELECT name, type_id
FROM table1
GROUP BY name, type_id
HAVING COUNT(*) > 1)
It would generally be more efficient to use analytic functions in order to avoid hitting the table a second time.
使用分析函数通常会更有效，以避免第二次碰到桌子。
SELECT *
FROM (SELECT id,
name,
type_id,
code,
lat,
long,
count(*) over (partition by name, type_id) cnt
FROM table1)
WHERE cnt > 1
Depending on what you are planning to do with the data and how many duplicates of a particular row there might be, you also might want to join table1to itself to get the data in a single row
根据您计划对数据执行的操作以及特定行可能有多少重复项，您还可能希望加入table1自身以获取单行中的数据
SELECT a.name,
a.type_id,
a.id,
b.id,
a.code,
b.code,
a.lat,
b.lat,
a.long,
b.long
FROM table1 a
JOIN table1 b ON (a.name = b.name AND
a.type_id = b.type_id AND
a.rowid > b.rowid)
回答by Zsolt Botykai
SELECT *
FROM table1 t1
WHERE (t1.name,t1.type_id) in ( SELECT DISTINCT name
, type_id
FROM table1
GROUP BY name, type_id
HAVING COUNT(*) > 1 )
Would do it.
会做的。
HTH
HTH
回答by The Nail
You can do a self join on the table to find all pairs of duplicates:
您可以在表上进行自联接以查找所有重复项对：
SELECT
a.name name
, a.type_id type_id_a
, a.code code_a
, a.lat lat_a
, a.long long_a
, b.code code_b
, b.lat lat_b
, b.long long_b
FROM table1 a
JOIN table1 b
ON a.name = b.name
AND a.type_id = b.type_id
AND a.ROWID > b.ROWID
To make sure that a row does not match itself and each pair is only output once, I added a.ROWID > b.ROWID, which works for Oracle. You will need a different way to keep them apart if you use a different database.
为了确保一行与自身不匹配并且每对只输出一次，我添加了a.ROWID > b.ROWID，它适用于 Oracle。如果您使用不同的数据库，您将需要一种不同的方式来将它们分开。
回答by Catherine Sudres
That still does not find the doubles if one of the compared fields has a NULL value. To get those, I use nvl to relace NULLs in the compared fields with a value that I know cannot occur in that table/field.
如果比较的字段之一具有 NULL 值，则仍然找不到双打。为了获得这些，我使用 nvl 将比较字段中的 NULL 与我知道在该表/字段中不能出现的值相关联。
回答by Rick
Just make the NULLS 0...
只需将 NULLS 设为 0...
...use NVL function.
...使用NVL功能。
