需求1:
计算得分率(%),需要将计算结果*100并保留一位小数
使用cast 函数
<span style="font-size:18px;">SELECT
titleName,
chapter,
CAST(
(
SUM(studentScore) / SUM(titleScore)
) * 100 AS DECIMAL (4, 1)
) AS ScoreRate
FROM
t_answerrecord
WHERE
classId = 1
AND paperId = 'CmX9kGRNoHunRoLGN8NTq1'
AND titletypesId = 'titletype5'
GROUP BY
titleId</span>
结果:
需求2:
显示行号
因为是mysql,不能使用row_number()函数,经过查询确定如下sql语句:
<span style="font-size:18px;"> SELECT @rowno:=@rowno+1 as rowno,r.* from t_article r,(select @rowno:=0) t</span>
<span style="font-size:18px;">SELECT @RowNo := @RowNo + 1 AS RowNo,r.titleName,
r.chapter,
CAST(
(
SUM(r.studentScore) / SUM(r.titleScore)
) * 100 AS DECIMAL (4, 1)
) AS ScoreRate
FROM t_answerrecord r ,(SELECT @RowNo:=0
FROM
t_answerrecord t)t
WHERE
classId = '1'
AND paperId = 'CmX9kGRNoHunRoLGN8NTq1'
AND titletypesId = 'titletype5'
and isDelete=0
GROUP BY
titleId</span>
结果:
小结:
sql语句要学习的很多,这篇博客作为积累,希望对大家有帮助。