PostgreSQL - 别名列和 HAVING

发布于 2024-12-05 21:38:52 字数 295 浏览 2 评论 0原文

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”不存在。

我怎样才能做这样的事情?

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;

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

How can I do something like this?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

深海蓝天 2024-12-12 21:38:52

您不能在having 中使用别名,并且必须在having 语句中重复该语句。由于您只想检查 null,因此可以这样做:

SELECT coalesce(SUM(X.Count)*3600, 0) AS PJZ, X.Mass
FROM X
WHERE X.Mass > 2000
HAVING ((X.Mass / coalesce(SUM(X.Count)*3600, 0) * 100) - 100) >= 10; 

You can't use aliases in a having, and have to duplicate the statement in the having cluause. Since you only want to check for null, you could do this:

SELECT coalesce(SUM(X.Count)*3600, 0) AS PJZ, X.Mass
FROM X
WHERE X.Mass > 2000
HAVING ((X.Mass / coalesce(SUM(X.Count)*3600, 0) * 100) - 100) >= 10; 
北风几吹夏 2024-12-12 21:38:52

其他选项是用 WITH 语句包围查询 - 例如:

WITH x as (
  SELECT coalesce(SUM(X.Count)*3600, 0) AS PJZ, X.Mass
  FROM X
  WHERE X.Mass > 2000
)
SELECT * from X WHERE PJZ >=10

在我看来,它比代码重复要好得多

Other option is to surround query by WITH statement - for example:

WITH x as (
  SELECT coalesce(SUM(X.Count)*3600, 0) AS PJZ, X.Mass
  FROM X
  WHERE X.Mass > 2000
)
SELECT * from X WHERE PJZ >=10

It is far better then code duplication in my opinion

陌伤浅笑 2024-12-12 21:38:52

将其包装到派生表中:(

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;

请注意,我添加了缺少的组,否则查询将无效)

Wrap it into a derived table:

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)

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文