SQL-选择两列中具有相同值的行 [英] SQL - select rows that have the same value in two columns

问题描述

该主题的解决方案正在逃避我.

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

NAME,CARDNUMBER,MEMBERTYPE

现在，我想要一个显示卡号和成员类型相同的行的视图.这两个字段都是整数.名称为VARCHAR.名称不是唯一的，并且相同的名称也应显示重复的卡号，会员类型.

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.

即如果是下表:

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

我想要:

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.

最有效的方法是什么?

推荐答案

由于您提到的名称可以重复，而且重复的名称仍表示是另一个人，因此应显示在结果集中，因此我们需要使用GROUP BY HAVING COUNT(*)> 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.

此外，由于从您的注释中发出的声音听起来像是要将其包装到视图中，因此您需要将子查询分开.

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小提琴示例

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