找到你要的答案

Q:Hide leading 0 on CAST SELECT output either in MySQL or PHP

Q:隐藏领先0铸选择MySQL和PHP的输出

Morning,

I currently have a SELECT statement calculating the PCT for a sports table:

SELECT team
     , COUNT(*) as played
     , SUM(win) as wins
     , SUM(loss) as lost
     , SUM(draw) as draws 
     , SUM(SelfScore) as ptsfor
     , SUM(OpponentScore) as ptsagainst
     , SUM((win*2 + draw)- loss) as score
     , CAST(SUM(win + (draw/2))/SUM(win + loss + draw) as decimal(4,3)) as pctWon

If the team has won all of their games, it displays as 1.000 which is correct.

I would like to remove the leading 0 if the result isn't 1.000. So for 50% wins it would display as .500 rather than the 0.500 it currently displays.

I'm not sure whether it would be easiest to do in PHP or MySQL.

Thanks

早晨,

我现在有一个选择语句计算一个运动表的PCT:

SELECT team
     , COUNT(*) as played
     , SUM(win) as wins
     , SUM(loss) as lost
     , SUM(draw) as draws 
     , SUM(SelfScore) as ptsfor
     , SUM(OpponentScore) as ptsagainst
     , SUM((win*2 + draw)- loss) as score
     , CAST(SUM(win + (draw/2))/SUM(win + loss + draw) as decimal(4,3)) as pctWon

如果球队赢得了所有的比赛,它显示为1,这是正确的。

我想删除领先0如果结果不是1。因此,对于50%胜它会显示为500,而不是目前显示的0.500。

我不确定它是否会是容易的事,在PHP或MySQL。

谢谢

answer1: 回答1:

This should work :

SELECT team
     , COUNT(*) as played
     , REPLACE(SUM(win), '0.', '.') as wins
     , SUM(loss) as lost
     , SUM(draw) as draws 
     , SUM(SelfScore) as ptsfor
     , SUM(OpponentScore) as ptsagainst
     , SUM((win*2 + draw)- loss) as score
     , CAST(SUM(win + (draw/2))/SUM(win + loss + draw) as decimal(4,3)) as pctWon

这应该工作:

SELECT team
     , COUNT(*) as played
     , REPLACE(SUM(win), '0.', '.') as wins
     , SUM(loss) as lost
     , SUM(draw) as draws 
     , SUM(SelfScore) as ptsfor
     , SUM(OpponentScore) as ptsagainst
     , SUM((win*2 + draw)- loss) as score
     , CAST(SUM(win + (draw/2))/SUM(win + loss + draw) as decimal(4,3)) as pctWon
php  mysql  casting  formatting  decimal