SELECT Name
,COUNT(1) NoOfScores
,MAX(Score)Max_Score
,MIN(Score)Min_Score
,ROUND(AVG(CAST(Score AS DECIMAL(18,2))),2,1)Avg_Score
FROM Table1
GROUP BY Name
,COUNT(1) NoOfScores
,MAX(Score)Max_Score
,MIN(Score)Min_Score
,ROUND(AVG(CAST(Score AS DECIMAL(18,2))),2,1)Avg_Score
FROM Table1
GROUP BY Name
-- 可以改成
,ROUND(AVG(CAST(Score * 1.0)),2,1)Avg_Score
Percentile_Disc 的语法比较复杂:
PERCENTILE_DISC ( numeric_literal ) WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )
OVER ( [ <partition_by_clause> ] )
Percentile_Disc(0.6)WITHIN GROUP (ORDER BY Score)
貌似是最高分,百分60的效果
,COUNT(1) NoOfScores
,MAX(Score)Max_Score
,MIN(Score)Min_Score
,Percentile_Disc(0.6)WITHIN GROUP (ORDER BY Score) Over()
FROM Table1
GROUP BY Name报错: Column 'Table1.Score' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.