SQL 选择具有相同值的多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9232986/
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
Select multiple rows with the same value(s)
提问by user1202673
I have a table, sort of like this:
我有一张桌子，有点像这样：
ID | Chromosome | Locus | Symbol | Dominance |
===============================================
1 | 10 | 2 | A | Full |
2 | 10 | 2 | a | Rec. |
3 | 10 | 3 | B | Full |
4 | 10 | 3 | b | Rec. |
I'd like to select all rows with the same locus and chromosome. For example, rows 3 and 4. There may be more than 2 at a time and they may not be in order.
我想选择具有相同基因座和染色体的所有行。例如，第 3 行和第 4 行。一次可能有 2 个以上，而且它们可能不按顺序排列。
I tried this:
我试过这个：
SELECT *
FROM Genes
GROUP BY Locus
HAVING Locus='3' AND Chromosome='10'
But it always returns row 3, never row 4, even when repeated. I think I'm missing something obvious and simple, but I'm at a loss.
但它总是返回第 3 行，从不返回第 4 行，即使重复时也是如此。我想我错过了一些明显而简单的东西，但我不知所措。
Can someone help?
有人可以帮忙吗？
回答by KM.
You need to understand that when you include GROUP BYin your query you are telling SQL to combine rows. you will get one row per unique Locusvalue. The Havingthen filters those groups. Usually you specify an aggergate function in the select list like:
您需要了解，当您GROUP BY在查询中包含时，您是在告诉 SQL 组合行。您将获得每个唯一Locus值的一行。在Having随后过滤这些组。通常你在选择列表中指定一个 agggate 函数，如：
--show how many of each Locus there is
SELECT COUNT(*),Locus FROM Genes GROUP BY Locus
--only show the groups that have more than one row in them
SELECT COUNT(*),Locus FROM Genes GROUP BY Locus HAVING COUNT(*)>1
--to just display all the rows for your condition, don't use GROUP BY or HAVING
SELECT * FROM Genes WHERE Locus = '3' AND Chromosome = '10'
回答by Matt Fenwick
Assuming that you want all rows for which there is another row with the exact same Chromosomeand Locus:
假设您想要所有具有完全相同Chromosome和的另一行的行Locus：
You can achieve this by joining the table to itself, but only returning the columns from one "side" of the join.
您可以通过将表连接到自身来实现这一点，但只从连接的“一侧”返回列。
The trick is to set the join condition to "the same locus and chromosome":
诀窍是将连接条件设置为“相同的基因座和染色体”：
select left.*
from Genes left
inner join Genes right
on left.Locus = right.Locus and
left.Chromosome = right.Chromosome and left.ID != right.ID
You can also easily extend this by adding a filter in a where-clause.
您还可以通过在where-clause 中添加过滤器来轻松扩展它。
回答by JYelton
The problem is GROUP BY- if you group results by Locus, you only get one result per locus.
问题是GROUP BY- 如果您按轨迹对结果进行分组，则每个轨迹只能获得一个结果。
Try:
尝试：
SELECT * FROM Genes WHERE Locus = '3' AND Chromosome = '10';
If you prefer using HAVINGsyntax, then GROUP BY idor something that is not repeating in the result set.
如果您更喜欢使用HAVING语法，那么GROUP BY id或者在结果集中不重复的东西。
回答by dasblinkenlight
This may work for you:
这可能对你有用：
select t1.*
from table t1
join (select t2.Chromosome, t2.Locus
from table2
group by t2.Chromosome, t2.Locus
having count(*) > 1) u on u.Chromosome = t1.Chromosome and u.Locus = t1.Locus
回答by dasblinkenlight
One way of doing this is via an existsclause:
这样做的一种方法是通过exists子句：
select * from genes g
where exists
(select null from genes g1
where g.locus = g1.locus and g.chromosome = g1.chromosome and g.id <> g1.id)
Alternatively, in MySQL you can get a summary of all matching ids with a single table access, using group_concat:
或者，在 MySQL 中，您可以使用单个表访问获取所有匹配 ID 的摘要group_concat：
select group_concat(id) matching_ids, chromosome, locus
from genes
group by chromosome, locus
having count(*) > 1
