如何在MySql中返回具有相同列值的行 [英] How to return rows that have the same column values in MySql
问题描述
让我们考虑下表-
ID Score
1 95
2 100
3 88
4 100
5 73
我是一个SQL新手,但是如何返回同时包含ID 2和ID 4的分数? 因此,它应该返回100,因为它同时在ID 2和ID 4中都具有特征
I am a total SQL noob but how do I return the Scores featuring both IDs 2 and 4? So it should return 100 since its featured in both ID 2 and 4
推荐答案
这是集内集"查询的示例.我建议使用having
子句进行聚合,因为这是最灵活的方法.
This is an example of a "sets-within-sets" query. I recommend aggregation with the having
clause, because it is the most flexible approach.
select score
from t
group by score
having sum(id = 2) > 0 and -- has id = 2
sum(id = 4) > 0 -- has id = 4
这是根据得分进行汇总.然后,having
子句的第一部分(sum(id = 2)
)计算每个分数有多少个"2".第二个是计算出多少个"4".仅返回分数为"2"和"4"的分数.
What this is doing is aggregating by score. Then the first part of the having
clause (sum(id = 2)
) is counting up how many "2"s there are per score. The second is counting up how many "4"s. Only scores that have at a "2" and "4" are returned.
这篇关于如何在MySql中返回具有相同列值的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!