1、删除不包括每个账号的最后十次登录日志：

DELETE FROM Sys_UserLoginLog WHERE KeyID NOT IN (
SELECT KeyID FROM
(
SELECT KeyID, ROW_NUMBER() OVER(PARTITION BY UserName ORDER BY LoginTime DESC) rowmuber FROM Sys_UserLoginLog
) t
WHERE t.rowmuber<=10
)

 

 

2、查询学生平均成绩及其名次 

SELECT AVG(Score) 平均成绩, Row_Number() OVER(ORDER BY Score DESC ) 名次 FROM tblScore GROUP BY Score--根据学生编号到新的序号