如何在MySql中返回具有相同列值的行

2021-11-20 00:00:00 sql mysql

让我们考虑下表-

ID Score
1  95

2  100

3  88

4  100

5  73

我是一个完全的 SQL 菜鸟，但如何返回具有 ID 2 和 4 的分数?所以它应该返回 100，因为它同时出现在 ID 2 和 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

推荐答案

这是一个sets-within-sets"查询示例.我建议使用 have 子句进行聚合，因为这是最灵活的方法.

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

这是按分数聚合的.然后 have 子句的第一部分 (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.

相关文章