mysql - 如何在此 sql 查询中将小数位数限制为 2？

Question

我有以下 SQL 查询:

select u.user_name, sum(r.goals) total_goals, sum(r.goals)/(2 * count(1)) avg_goals 
from (select community_id, player1_id id, player1_goals goals 
from results union all select community_id, player2_id, player2_goals from results) r 
inner join users u on r.id = u.id 
where r.community_id = 16 group by r.id, u.user_name 
ORDER BY avg_goals DESC

产生的结果如下"avg_goals", "1.2500"(对于这个例子，我只对这一列感兴趣)

如何将结果中的小数位数限制为 2 位？所以输出将是 1.25 而不是 1.2500

Answer 1

你可以使用ROUND(col, 2)

select u.user_name, sum(r.goals) total_goals, round(sum(r.goals)/(2 * count(1)),2) avg_goals 
from (select community_id, player1_id id, player1_goals goals 
from results union all select community_id, player2_id, player2_goals from results) r 
inner join users u on r.id = u.id 
where r.community_id = 16 group by r.id, u.user_name 
ORDER BY avg_goals DESC

正如@scaisEdge 所说，如果您只想截断其余数字，也可以使用 truncate(col,2)。