MySQL - 百分比计算并在同一表的其他列中更新

我会把你的计算写成：

SELECT `G1%`, 
       (1 - ranks / totals) * 100 as Percentile
FROM (SELECT `G1%`,
              (@rank := @rank + 1) ranks,
              (SELECT COUNT(*) FROM PCount) as totals
      FROM (SELECT DISTINCT `G1%`
            FROM PCount
            ORDER BY `G1%` DESC
           ) p CROSS JOIN
           (SELECT COUNT(*) as totals, @rank := 0
            FROM Pcount
           ) params
     ) p;

我做了一些更符合 MySQL 如何处理变量的更改。特别是，SELECT DISTINCT和ORDER BY位于子查询中。这在较新版本的 MySQL 中是必需的（尽管在最新版本中您可以使用窗口函数）。

现在可以使用JOIN以下方法将其合并到更新中：

UPDATE PCount p JOIN
       (SELECT `G1%`, 
                (1 - ranks / totals) * 100 as Percentile
        FROM (SELECT `G1%`,
                      (@rank := @rank + 1) ranks,
                      (SELECT COUNT(*) FROM PCount) as totals
              FROM (SELECT DISTINCT `G1%`
                    FROM PCount
                    ORDER BY `G1%` DESC
                   ) p CROSS JOIN
                   (SELECT COUNT(*) as totals, @rank := 0
                    FROM Pcount
                   ) params
             ) pp
       ) pp
       ON pp.`G1%` = p.`G1%`
    SET p.G1Ptile = pp.percentile;