在 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屋！