表score (ID,UserID,PaperID,PaperName,Score)
SELECT
PaperName ,
UserID ,
Score ,
RANK() OVER ( PARTITION BY PaperName ORDER BY Score DESC ) AS 名次
FROM Score where PaperName='测试1'
可以查询出测试1的考生名次,但是我希望加一列参加考试1的总人数:
SELECT
count(PaperName),
PaperName ,
UserID ,
Score ,
RANK() OVER ( PARTITION BY PaperName ORDER BY Score DESC ) AS 名次
FROM Score where PaperName='测试1'
加了count(PaperName) 出现提示消息 8120,级别 16,状态 1,第 1 行
选择列表中的列 'Score.PaperName' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
请大虾帮我找找原因
------解决方案--------------------
- SQL code
SELECT b.CNT , Score.PaperName , UserID , Score , RANK() OVER ( PARTITION BY Score.PaperName ORDER BY Score DESC ) AS 名次FROM Score inner join (select PaperName,CNT=count(1) from score(nolock) group by PaperName) b on score.PaperName=b.PaperNamewhere Score.PaperName='测试1'