问题描述
在理解查询别名在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中访问列别名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!