sql - 选择具有相同值的多行

Question

我有一张 table ，有点像这样:

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 个以上，而且它们可能不按顺序排列。

我试过这个:

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

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

有人可以帮忙吗？

Answer 1

您需要了解当您包含 GROUP BY 时在您的查询中，您告诉 SQL 组合行。每个唯一的您将获得一行 Locus值(value)。 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'