当前位置: 代码迷 >> Sql Server >> Count使用时提示异常
  详细解决方案

Count使用时提示异常

热度:36   发布时间:2016-04-27 11:30:49.0
Count使用时提示错误
表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'
  相关解决方案