通过字段的 MIN 函数组从一组项目中排除一行 [英] Exempting a row from a group of items by MIN function group by a field
问题描述
我希望输出能够为每个班级和年级以及按班级分组的每个学生填充最低分数.我的查询看起来像这样，但它会为我返回所有记录.同时，我只想要一个班级中分数最低的记录.
I'll like the output to populate the minimum score for each student in each class and year and group by class. My query looks like this but it's returning all records for me. Meanwhile, I only want the the records where score is minimum in a class.
从 MyTable 中选择年份、学生、班级、MIN(Score)；
SELECT Year, Student, Class, MIN(Score) FROM MyTable;
谢谢！
|2001 |骗局 |1 |30 |
| 2001 | Con | 1 | 30 |
|2001 |实验室 |1 |50 |
| 2001 | Lab | 1 | 50 |
|2001 |图书馆 |1 |10 |
| 2001 | Lib | 1 | 10 |
|2001 |工业|1 |5 |
| 2001 | Ind | 1 | 5 |
|2001 |骗局 |2 |40 |
| 2001 | Con | 2 | 40 |
|2001 |实验室 |2 |35 |
| 2001 | Lab | 2 | 35 |
|2001 |图书馆 |2 |50 |
| 2001 | Lib | 2 | 50 |
|2001 |工业|2 |80 |
| 2001 | Ind | 2 | 80 |
|2005 |骗局 |1 |10 |
| 2005 | Con | 1 | 10 |
|2005 |实验室 |1 |20 |
| 2005 | Lab | 1 | 20 |
|2005 |图书馆 |1 |15 |
| 2005 | Lib | 1 | 15 |
|2005 |工业|1 |30 |
| 2005 | Ind | 1 | 30 |
|2005 |骗局 |2 |50 |
| 2005 | Con | 2 | 50 |
|2005 |实验室 |2 |40 |
| 2005 | Lab | 2 | 40 |
|2005 |图书馆 |2 |15 |
| 2005 | Lib | 2 | 15 |
|2005 |工业|2 |35 |
| 2005 | Ind | 2 | 35 |
|2010 |骗局 |1 |60 |
| 2010 | Con | 1 | 60 |
|2010 |实验室 |1 |40 |
| 2010 | Lab | 1 | 40 |
|2010 |图书馆 |1 |50 |
| 2010 | Lib | 1 | 50 |
|2010 |工业|1 |70 |
| 2010 | Ind | 1 | 70 |
|2010 |骗局 |2 |10 |
| 2010 | Con | 2 | 10 |
|2010 |实验室 |2 |20 |
| 2010 | Lab | 2 | 20 |
|2010 |图书馆 |2 |15 |
| 2010 | Lib | 2 | 15 |
|2010 |工业|2 |30 |
| 2010 | Ind | 2 | 30 |
推荐答案
这样的事情可能会奏效:
Something like this may work:
选择 T1.*从 MyTable AS T1右外连接(SELECT Class, MIN(Score) AS MinScore从我的表按班级分组) 作为 T2ON T1.Class = T2.ClassAND T1.Score = T2.MinScore
SELECT T1.* FROM MyTable AS T1 RIGHT OUTER JOIN ( SELECT Class, MIN(Score) AS MinScore FROM MyTable GROUP BY Class ) AS T2 ON T1.Class = T2.Class AND T1.Score = T2.MinScore
这篇关于通过字段的 MIN 函数组从一组项目中排除一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
