sql语句计算保留小数位并加上行号

需求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语句要学习的很多，这篇博客作为积累，希望对大家有帮助。