在PostgreSQL中访问列别名 [英] Accessing column alias in postgresql

问题描述

在理解查询别名在Postgresql中的工作方式时会遇到一些麻烦. 我有以下内容:

Having a little bit of trouble understanding how a query alias works in postgresql. I have the following:

SELECT DISTINCT robber.robberid,
                nickname,
                Count(accomplices.robberid) AS count1
FROM   robber
       INNER JOIN accomplices
               ON accomplices.robberid = robber.robberid
GROUP  BY robber.robberid,
          robber.nickname
ORDER  BY Count(accomplices.robberid) DESC;


 robberid |            nickname            | count1 
----------+--------------------------------+--------
       14 | Boo Boo Hoff                   |      7
       15 | King Solomon                   |      7
       16 | Bugsy Siegel                   |      7
       23 | Sonny Genovese                 |      6
        1 | Al Capone                      |      5
 ...

我可以使用as命令重命名"count1"列，但似乎无法在查询中再次引用它吗?我试图在此查询的末尾包含一个HAVING命令，以仅查询计数少于最大值一半的对象.

I can rename the "count1" column using the as command but I can't seem to be able to refer to this again in the query? I am trying to include a HAVING command at the end of this query to query only objects who have a count less than half of the max.

这是家庭作业，但我并不是在寻求答案，而是一个指向如何在其他子句中包含count1列的指针.

This is homework but I am not asking for the answer only a pointer to how I can include the count1 column in another clause.

任何人都可以帮忙吗?

推荐答案

通常，您以后不能在查询中引用聚合列的别名，而必须重复聚合

In general, you can't refer to an aggregate column's alias later in the query, and you have to repeat the aggregate

如果您确实要使用其名称，则可以将查询包装为子查询

If you really want to use its name, you could wrap your query as a subquery

SELECT * 
FROM
(
    SELECT DISTINCT robber.robberid, nickname, count(accomplices.robberid)  
    AS count1 FROM robber                   
    INNER JOIN accomplices  
    ON accomplices.robberid = robber.robberid  
    GROUP BY robber.robberid, robber.nickname  
) v
ORDER BY count1 desc

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