PostgreSQL-别名列和HAVING [英] PostgreSQL - Aliases column and HAVING

问题描述

SELECT  
CASE WHEN SUM(X.Count)*3600 is null THEN  '0'  
            ELSE  
            SUM(X.Count)*3600  
       END AS PJZ,  
       X.Mass  
FROM X  
WHERE X.Mass > 2000  
HAVING ((X.Mass / PJZ * 100) - 100) >= 10;

获取：错误：»pjz«列不存在。

Getting: ERROR: Column »pjz« doesn't exists.

我该怎么做？

推荐答案

将其包装到派生表中：

SELECT CASE 
          WHEN PJZ = 0 THEN 100
          ELSE PJZ
       END as PJZ,
       mass
FROM (
    SELECT CASE 
             WHEN SUM(X.Count)*3600 is null THEN '0'  
             ELSE SUM(X.Count)*3600  
           END AS PJZ,  
           X.Mass  
    FROM X  
    WHERE X.Mass > 2000  
    GROUP BY X.mass
) t
WHERE PJZ = 0 
   OR ((X.Mass / PJZ * 100) - 100) >= 10;

（请注意，我添加了缺少的组，否则查询将无效）

(Note that I added the missing group by as otherwise the query would not be valid)

这篇关于PostgreSQL-别名列和HAVING的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！