按分数动态标记用户

可以使用交叉联接将用户联接到1行统计信息查询。然后可以使用嵌套的IIF来计算等级。

像这样的…

SELECT  users.*,grade.*
,iif(users.score>grade.high,"A",iif(users.score>grade.average,"B",iif(users.score>grade.low,"C","D"))) as label
FROM (SELECT round(avg(users.score)-stdev(users.score),1) as low
,round(avg(users.score),1) as average
,round(avg(users.score)+stdev(users.score),1) as high
FROM users)  AS grade, users;

IIF成功了。

我采用了平均分的查询法来添加最低A、B和C分。

Table(avg,stdev,Ascore,Bscore,Cscore) as averages

最后一个查询看起来像

SELECT user.Id, user.avgScore, 
IIf(avgScore>averages.Ascore,"A",
 IIf(avgScore>averages.Bscore,"B",
  IIf(avgScore>averages.Cscore,"C","D"))) AS label
FROM averages, users