MySql 十进制问题输出

Question

晚上

我有一个运动表，它按成功百分比排序((赢得的比赛+(平局的比赛/2)/玩的比赛)，我在输出后如果100％的比赛获胜，那么它读作1.000、75% 然后 0.750、50% 作为 0.500 等(删除前导 0)。

目前我可以让它显示0.000到0.950，如果球队只打了1场比赛并获胜，那么输出显示0.100。

我怎样才能:a) 让它显示 1 场比赛/获胜次数为 1.000b) 以百分比形式显示和其他组合，如 0.500

有什么想法吗？

谢谢

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))/(count(*)) as decimal(3,3)) pctWon
FROM (
      SELECT team
     , SelfScore
     , OpponentScore
     , SelfScore > OpponentScore win
     , SelfScore < OpponentScore loss
     , SelfScore = OpponentScore draw
      FROM (
        SELECT HomeTeam team, HomeScore SelfScore, AwayScore OpponentScore
        FROM Game
        WHERE Season = '2015' and Type = 'League'
        UNION ALL 
        SELECT AwayTeam, AwayScore, HomeScore
        FROM Game
        WHERE Season = '2015' and Type = 'League'
       ) a
) b
GROUP BY team
ORDER BY pctWon DESC, score DESC, ptsagainst DESC, team ASC;
        ");

这会生成一个看起来正确的表格，但前 2 个显示的 PCT 不正确。

Team    W   L   T   PF  PA  PCT
T1      1   0   0   54  0   0.100
T2      1   0   0   44  0   0.100
T3      0   1   0   0   54  0.000
T4      0   1   0   0   44  0.000
T5      6   2   2   220 122 0.700
T6      7   3   0   247 139 0.700
T7      6   4   0   191 191 0.600
T8      4   5   1   167 201 0.450
T9      3   6   1   142 202 0.350
T10     2   8   0   193 305 0.200

谢谢

Answer 1

DECIMAL(3,3) 小数点左侧没有空格。您需要DECIMAL(4,3)。

您使用的 MySQL 版本是什么？ 0.100 听起来像是一个错误。但我无法在 5.6.12 上重现它

mysql> SELECT CAST(((1+0/2)/1) AS DECIMAL(3,3));
+-----------------------------------+
| CAST(((1+0/2)/1) AS DECIMAL(3,3)) |
+-----------------------------------+
|                             0.999 |
+-----------------------------------+

请注意，(4,3) 可以满足您的需求:

mysql> SELECT CAST(((1+0/2)/1) AS DECIMAL(4,3));
+-----------------------------------+
| CAST(((1+0/2)/1) AS DECIMAL(4,3)) |
+-----------------------------------+
|                             1.000 |
+-----------------------------------+