mysql - 计算别名

Question

我有这个问题

SELECT number_title,
   (SELECT COUNT(*)
    FROM poll_stemmen
    )   AS total_number,

    (SELECT COUNT(ps.number_id)
            FROM poll_stemmen
             group by ps.number_id
            ) AS voted
   FROM poll_numbers AS pn
   RIGHT JOIN poll_stemmen AS ps ON ps.number_id = pn.id
   GROUP BY ps.number_id

但我想在 voted 和 total_number 字段之间进行计算

所以我做了这个查询:

SELECT number_title,
   (SELECT COUNT(*)
    FROM poll_stemmen
    )   AS total_number,

    (SELECT COUNT(ps.number_id)
            FROM poll_stemmen
             group by ps.number_id
            ) AS voted, (SELECT voted) AS another_number

         FROM poll_numbers AS pn
         RIGHT JOIN poll_stemmen AS ps ON ps.number_id = pn.id
         GROUP BY ps.number_id

但是我得到这个错误:

Reference 'voted' not supported (reference to group function)

我该如何解决这个问题？

Answer 1

您可以将查询包装在子查询中。然后在外部查询中以任何你喜欢的方式使用它的别名，例如

SELECT number_title, total_number, voted, 
       total_number * voted AS myCalculatedColumn
FROM (
SELECT number_title,
       (SELECT COUNT(*)
        FROM poll_stemmen) AS total_number,    
       (SELECT COUNT(ps.number_id)
        FROM poll_stemmen
        group by ps.number_id) AS voted
FROM poll_numbers AS pn
RIGHT JOIN poll_stemmen AS ps ON ps.number_id = pn.id
GROUP BY ps.number_id ) t