“字段列表”中的未知列“结果1”

Question

我试图对result1和result2的值求和，但结果是未知的列result1。有人能帮忙吗？我做错了什么？

SELECT *
      ,case when event1 = '100mh' then 25.4347*power(sum(18.00-10.40),1.81) end as result1
      ,case when event2 = 'highjump' then 9.4347*power(sum(14.00-145),1.835) end as result2
      ,SUM(result1 + result2 )as total
from heptathlon 
group by athlete_id

Answer 1

试试这个：

SELECT t.*, sum(t.result1 + t.result2) AS total
  FROM (  SELECT b.*,
                 CASE
                    WHEN b.event1 = '100mh'
                    THEN
                       25.4347 * POWER (SUM (18.00 - 10.40), 1.81)
                 END
                    AS result1,
                 CASE
                    WHEN b.event2 = 'highjump'
                    THEN
                       9.4347 * POWER (SUM (14.00 - 145), 1.835)
                 END
                    AS result2
            FROM heptathlon b
        GROUP BY b.athlete_id) t