我正在尝试执行此查询。这是我的查询语句:
我的数据表为: Table
StudyID FacultyID Year Access1 Access2 Access3
1 1 2014 4 8 5
1 2 2014 8 4 7
1 1 2013 5 4 4
2 3 2014 4 6 5
2 5 2013 5 8 10
2 4 2014 5 5 7
3 7 2013 9 4 7
我想按照StudyID和Year分组,并获取每个字段Access1、Access2和Access3的最小值,并仅显示最后一年,也就是每个组的第一行。以下是结果。
StudyID Year Access1 Access2 Access3
1 2014 4 4 5
2 2014 4 5 5
3 2013 9 4 7
这是我的查询:
SELECT DISTINCT T.StudyID, T.Year, MIN(T.Access1), MIN(T.Access2), MIN(T.Access3)
FROM T
GROUP BY T.StudyID, T.Year
ORDER BY T.StudyID, T.Year DESC
我也试过这一个。
;WITH MyQuery AS ( SELECT DISTINCT T.StudyID, T.Year, MIN(T.Access1), MIN(T.Access2), MIN(T.Access3),ROW_NUMBER() OVER (PARTITION BY T.StudyID, T.Year ORDER BY T.StudyID, T.Year DESC) AS rownumber
FROM T GROUP BY T.StudyID, T.Year ORDER BY T.StudyID , T.Year DESC ) SELECT * FROM MyQuery WHERE rownumber = 1
任何时候,如果成功没有到来,我知道我缺少了某些东西...但不知道是什么? 提前感谢!!!