无法化名为简单的CASE语句

Question

大家好，我是MySQL服务器的新手，正在努力完成课堂作业。我似乎找不到解决办法。任何帮助都是非常感谢的。这是我的代码，这是错误声明。提前谢谢你。

错误声明：

错误1054 (42S22)在第1行：“字段列表”中未知列“Total_Number_Forced_Outage_Events”

SELECT 
 
SUM(CASE 

WHEN Reason = 'Forced' THEN 1 ELSE 0 END) AS Total_Number_Forced_Outage_Event,

Count(*)AS Total_Number_Outage_Events,

Total_Number_Forced_Outage_Events / Total_Number_Outage_Events AS Forced_Outage_Percentage,

YEAR(Start_Time) AS Year

FROM AEMR 

WHERE Status = 'Approved' 

GROUP BY Year 

ORDER BY Year 

;

Answer 1

正如上面评论的那样，您不能在定义别名的同一个查询中引用别名。

但是，您可以在子查询中定义别名，并在外部查询中使用它：

SELECT t.*,
  Total_Number_Forced_Outage_Events / Total_Number_Outage_Events
    AS Forced_Outage_Percentage
FROM (
    SELECT 
      SUM(CASE WHEN Reason = 'Forced' THEN 1 ELSE 0 END)
        AS Total_Number_Forced_Outage_Events,
      COUNT(*) AS Total_Number_Outage_Events,
      YEAR(Start_Time) AS Year
    FROM AEMR 
    WHERE Status = 'Approved' 
    GROUP BY Year 
) AS t
ORDER BY Year;

本例中的t是子查询的表别名。

例外情况: MySQL允许您使用在GROUP BY、HAVING和ORDER BY子句中相同的查询中定义的别名。

还请阅读有关https://dev.mysql.com/doc/refman/8.0/en/select.html和https://dev.mysql.com/doc/refman/8.0/en/problems-with-alias.html中的列别名的文档。

Answer 2

CASE WHEN Reason = 'Forced' THEN 1 ELSE 0 END与Reason = 'Forced'完全相同，因为MySQL (和MariaDB)使用1和0作为布尔值。

所以您可以使用SUM(Reason = 'Forced')获取Total_Number_Forced_Outage_Events。

而且，COUNT(Reason = 'Forced')会给出Total_Number_Outage_Events，因为0和1都会被计算(这在你的情况下并不特别有用，但它解释了为什么下一句是真的)。

最后，AVG(Reason = 'Forced')将给您Forced_Outage_Percentage (因为AVG(x)本质上是SUM(x) / COUNT(x))。

因此，编写查询的一种方法是：

SELECT 
  SUM(Reason = 'Forced') AS Total_Number_Forced_Outage_Event,
  COUNT(Reason = 'Forced') AS Total_Number_Outage_Events,
  AVG(Reason = 'Forced') AS Forced_Outage_Percentage,
  YEAR(Start_Time) AS Year
FROM AEMR 
WHERE Status = 'Approved' 
GROUP BY Year 
ORDER BY Year 
;

我认为它足够简洁。

请注意，编写COUNT(Reason = 'Forced')而不是COUNT(*)可能更有效，因为优化器可能会看到SUM(x)、COUNT(x)和AVG(x)在同一范围内使用，并同时执行这三种计算。