sql - MySQL使用计算返回值来计算其他值

Question

假设我有一个包含以下内容的选择语句:

SUM(st.tafPoints/200) as totalFriendReferrals,
SUM(CASE WHEN st.gender = "m" THEN st.tafPoints/200 ELSE 0 END) as maleFriendReferrals,
SUM(CASE WHEN st.gender = "f" THEN st.tafPoints/200 ELSE 0 END) as femaleFriendReferrals

我需要根据 totalFriendReferrals 计算 maleFriendReferrals 的百分比。这些都是派生的(即，最初不在列列表中)值。所以如果我尝试做类似的事情:

CONCAT( (maleFriendReferrals/totalFriendReferrals) *100 , '%') as malePercentReferrals

但是我得到一个错误:

Unknown column 'maleFriendReferrals' in 'field list' – 1 ms

有没有办法轻松做到这一点？我需要能够在查询本身中执行此操作，而不必遍历我的结果来计算百分比。

Answer 1

您应该将原始查询放入子查询

SELECT CONCAT( (maleFriendReferrals/totalFriendReferrals) *100 , '%') as malePercentReferrals
FROM
  (SELECT
    SUM(st.tafPoints/200) as totalFriendReferrals,
    SUM(CASE WHEN st.gender = "m" THEN st.tafPoints/200 ELSE 0 END) as maleFriendReferrals,
    SUM(CASE WHEN st.gender = "f" THEN st.tafPoints/200 ELSE 0 END) as femaleFriendReferrals
  FROM st) AS subquery