问题描述
让我们考虑下表-
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.
这篇关于如何在MySql中返回具有相同列值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！