日期:2014-05-17  浏览次数:20516 次

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.PaperName
where Score.PaperName='测试1'