问题描述
我进行了搜索,发现的所有内容均指记录中同一字段的重复项.我使用的数据库存储计算机硬件,其中包含 ip 地址的 en0 和 en1 字段.我需要找到存在于任一字段中的具有重复 ip 的记录.为简单起见,这里有一个示例表:
I have searched and everything I found refers to duplicates of the same field on a record. The database I am using stores computer hardware and among the fields are en0 and en1 which contain ip addresses. I need to find records with duplicate ips that exist in either field. For simplicity here is a sample table:
id serial_no en0 en1
1 0000001 10.200.5.102 10.200.5.103
2 0000002 10.200.6.102 10.200.6.103
3 0000003 10.200.5.110 10.200.5.102
我需要查询返回记录 1 和/或 3.获取任何一个重复都可以,最好同时返回.
I need the query to return records 1 and/or 3. Getting either duplicate is ok, best would be to return both.
谢谢
推荐答案
您可以使用以下查询:
SELECT DISTINCT id, serial_no, en0, en1
FROM mytable
INNER JOIN (SELECT ip
FROM (
SELECT id, serial_no, en0 AS ip
FROM mytable
UNION
SELECT id, serial_no, en1 AS ip
FROM mytable ) t
GROUP BY ip
HAVING COUNT(*) > 1) t
ON t.ip = en0 OR t.ip = en1
以上将返回所有包含重复IP的记录.
The above will return all records containing duplicate IPs.
以下子查询:
SELECT ip
FROM (
SELECT id, serial_no, en0 AS ip
FROM mytable
UNION
SELECT id, serial_no, en1 AS ip
FROM mytable ) t
GROUP BY ip
HAVING COUNT(*) > 1;
用于返回重复的 IP.注意使用 UNION
而不是 UNION ALL
.这样,在 same 记录的 en0
、en1
列中重复的 IP 将不会被视为重复.如果您想要不同的行为,请更改为 UNION ALL
.
is used to return duplicate IPs. Note the use of UNION
instead UNION ALL
. This way an IP that is repeated in columns en0
, en1
of the same record will not be considered as duplicate. Change to UNION ALL
if you want different behavior.
这篇关于查询以查找不同字段中的重复项的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!