在多列中查找具有相同值的行 [英] Finding rows with same values in multiple columns

问题描述

我正在尝试查找具有重复值的行，但仅基于选定的列数，而不是单列或整行.例如，如果我的表是这样的:

I am trying to find rows that have duplicate values, but only based off of a select number of columns, not a single column or the entire row. For example, if my table looked like this:

ID     Address    State    Name
-------------------------------
0      7 Brown    NY       John
1      3 Red      WX       Jane
2      7 Brown    WX       Ted
3      7 Brown    NY       Fred

我的问题是:

查找行的地址和州字段与另一行的地址和州字段匹配的行的所有 ID.

Find all ID's for rows where the row's Address and State field matched another row's Address and State field.

这个查询的答案是:

ID    Address    State    Name
------------------------------
0     7 Brown    NY       John
3     7 Brown    NY       Fred

有什么想法吗?

建议:如何从单行中选择多列值相同的行表

推荐答案

尝试以下操作:

SELECT A.*
FROM YourTable A
INNER JOIN (SELECT Address, State
            FROM YourTable
            GROUP BY Address, State
            HAVING COUNT(*) > 1) B
ON A.Address = B.Address AND A.State = B.State

这篇关于在多列中查找具有相同值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！