我有一个数据库用于辩论比赛,我正在尝试对成绩进行排序,以查看哪些学校将在学期末参加决赛。
如下所示,我已经做到了这一点,但是我无法将ptc字段四舍五入,它目前返回最多4位小数,例如如果他们赢得了3场比赛中的2场,则为0.6667。
SELECT t.id,
t.name AS name,
SUM(t.id = d.winnerid) AS w,
SUM(t.id != d.winnerid) AS l,
SUM(t.id = d.winnerid)/(SUM(t.id = d.winnerid)+SUM(t.id != d.winnerid)) AS ptc
FROM debates AS d
JOIN teams AS t ON t.id IN (d.hostid, d.visitid)
WHERE d.visitid != -1
AND d.debatedate < CURDATE()
GROUP BY t.id
ORDER BY ptc DESC
我认为我必须在这里使用decimal(2,2),但我无法正确使用语法,我尝试了一些不同的东西,例如:
SUM(t.id = d.winnerid)/(SUM(t.id = d.winnerid)+SUM(t.id != d.winnerid)) AS ptc decimal (2,2)
如果需要,我很乐意提供有关表格的更多信息,但我不认为有必要吗?