SQL - 选择两列中具有相同值的行

The solution to the topic is evading me.

该主题的解决方案正在回避我。

I have a table looking like (beyond other fields that have nothing to do with my question):

我有一张桌子(除了与我的问题无关的其他领域):

NAME,CARDNUMBER,MEMBERTYPE

Now, I want a view that shows rows where the cardnumber AND membertype is identical. Both of these fields are integers. Name is VARCHAR. Name is not unique, and duplicate cardnumber, membertype should show for the same name, as well.

现在,我想要一个显示cardnumber和membertype相同的行的视图。这两个字段都是整数。名称是VARCHAR。名称不是唯一的,并且重复的cardnumber,membertype也应该显示相同的名称。

I.e. if the following was the table:

即如果以下是表格:

JOHN       | 324   | 2
PETER      | 642   | 1
MARK       | 324   | 2
DIANNA     | 753   | 2
SPIDERMAN  | 642   | 1
JAMIE FOXX | 235   | 6

I would want:

我想要:

JOHN       | 324   | 2
MARK       | 324   | 2
PETER      | 642   | 1
SPIDERMAN  | 642   | 1

this could just be sorted by cardnumber to make it useful to humans.

这可以按卡号排序,以使其对人类有用。

What's the most efficient way of doing this?

这样做最有效的方法是什么?

3 个解决方案

#1

Since you mentioned names can be duplicated, and that a duplicate name still means is a different person and should show up in the result set, we need to use a GROUP BY HAVING COUNT(*) > 1 in order to truly detect dupes. Then join this back to the main table to get your full result list.

由于您提到名称可以重复,并且重复名称仍然意味着是一个不同的人并且应该显示在结果集中,我们需要使用GROUP BY HAVING COUNT(*)> 1来真正检测欺骗。然后将其连接回主表以获取完整的结果列表。

Also since from your comments, it sounds like you are wrapping this into a view, you'll need to separate out the subquery.

此外,从您的评论中,听起来您将其包装到视图中,您需要将子查询分开。

CREATE VIEW DUP_CARDS
AS
SELECT CARDNUMBER, MEMBERTYPE
FROM mytable t2
GROUP BY CARDNUMBER, MEMBERTYPE
HAVING COUNT(*) > 1

CREATE VIEW DUP_ROWS
AS
SELECT t1.*
FROM mytable AS t1
INNER JOIN DUP_CARDS AS DUP
ON (T1.CARDNUMBER = DUP.CARDNUMBER AND T1.MEMBERTYPE = DUP.MEMBERTYPE )

SQL Fiddle Example

SQL小提琴示例

#2

You can use exists for this:

你可以使用exists来实现:

select * 
from yourtable y
where exists (
  select 1
  from yourtable y2 
  where y.name <> y2.name
    and y.cardnumber = y2.cardnumber
    and y.membertype = y2.membertype)

SQL Fiddle Demo

SQL小提琴演示

#3

What's the most efficient way of doing this?

这样做最有效的方法是什么?

I believe a JOIN will be more efficient than EXISTS

我相信JOIN会比EXISTS更有效率

SELECT t1.* FROM myTable t1
JOIN (
    SELECT cardnumber, membertype
    FROM myTable
    GROUP BY cardnumber, membertype
    HAVING COUNT(*) > 1
) t2 ON t1.cardnumber = t2.cardnumber AND t1.membertype = t2.membertype

Query plan: http://www.sqlfiddle.com/#!2/0abe3/1

查询计划:http://www.sqlfiddle.com/#!2 / obab3 / 1

#1