MySQL SQL - 选择在两列中具有相同值的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23860490/
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
SQL - select rows that have the same value in two columns
提问by nickdnk
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 AND membertype 相同的行。这两个字段都是整数。名称是 VARCHAR。姓名不是唯一的，重复的卡号、会员类型也应显示为相同的姓名。
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?
这样做的最有效方法是什么？
采纳答案by woot
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 )
回答by FuzzyTree
What's the most efficient way of doing this?
这样做的最有效方法是什么？
I believe a JOINwill be more efficient than EXISTS
我相信 aJOIN会比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
回答by sgeddes
You can use existsfor 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)
回答by thomas
If you just need to know the valuepairsof the 3 fields that are not unique then you could simply do:
如果您只需要知道不是唯一的 3 个字段的值对，那么您可以简单地执行以下操作：
SELECT concat(NAME, "|", CARDNUMBER, "|", MEMBERTYPE) AS myIdentifier,
COUNT(*) AS count
FROM myTable
GROUP BY myIdentifier
HAVING count > 1
This will give you all the different pairs of NAME, CARDNUMBERand MEMBERTYPEthat are used more than once with a count (how many times they are duplicated). This doesnt give you back the entries, you would have to do that in a second step.
这将为您提供所有不同的NAME,CARDNUMBER并且MEMBERTYPE使用计数不止一次（它们被复制了多少次）。这不会让您返回条目，您必须在第二步中执行此操作。
