mysql - 获取mysql中某个值出现的百分比2

Question

除了 this question 之外，我还必须尊重另一列“post_id”:

ID | name | version | post_id
=============================
 1 | foo  | 1.0     |  2
 2 | bar  | 1.0     |  2
 3 | loo  | 1.1     |  1
 4 | zoo  | 1.2     |  2

现在的输出应该是:

version | percentage | post_id
===============================
 1.0    | 0.667      | 2
 1.1    | 1.000      | 1
 1.2    | 0.333      | 2

The solution by Gordon Linoff 工作得很好，除了它计算所有条目的百分比(显然):

SELECT version, 
       Count(*) / const.cnt 
FROM   mytable 
       CROSS JOIN (SELECT Count(*) AS cnt 
                   FROM   mytable) const 
GROUP  BY version 
ORDER  BY version;

编辑(这不是我想要的!)

仅将 post_id 添加到选择中不会带来预期的结果:

SELECT version, 
       Count(*) / const.cnt,
       post_id
FROM   mytable 
       ....

输出将是

version | percentage | post_id
===============================
 1.0    | 0.500      | 2
 1.1    | 0.250      | 1
 1.2    | 0.250      | 2

编辑这是第一个想法的 fiddle :http://sqlfiddle.com/#!2/f738d/1

Answer 1

认为这样就可以了:-

SELECT version, 
       Count(*) / const.cnt ,
       mytable.post_id
FROM   mytable 
INNER JOIN (SELECT post_id, Count(*) AS cnt 
           FROM   mytable
           GROUP BY post_id) const 
ON mytable.post_id = const.post_id
GROUP  BY version, post_id
ORDER  BY version;

子查询获取每个帖子 ID 的记录数，然后使用此计数来计算该版本的分数。