选择具有相同值的多行 [英] Select multiple rows with the same value(s)

问题描述

我有一张桌子，有点像这样:

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.    |

我想选择具有相同基因座和染色体的所有行.例如，第 3 行和第 4 行.一次可能有 2 个以上，它们可能没有顺序.

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.

我试过这个:

SELECT *
FROM Genes
GROUP BY Locus
HAVING Locus='3' AND Chromosome='10'

但它总是返回第 3 行，从不返回第 4 行，即使重复.我想我错过了一些明显而简单的东西，但我不知所措.

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.

有人可以帮忙吗?

推荐答案

您需要了解，当您在查询中包含 GROUP BY 时，您是在告诉 SQL 组合行.每个唯一的 Locus 值将获得一行.Having 然后过滤这些组.通常你在选择列表中指定一个 agggate 函数，如:

You need to understand that when you include GROUP BY in your query you are telling SQL to combine rows. you will get one row per unique Locus value. The Having then filters those groups. Usually you specify an aggergate function in the select list like:

--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'

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