在Over（）条款中使用别名

发布于 2024-10-29 05:08:47 字数 354 浏览 3 评论 0原文

我可以通过 row_number() OVER() 按计数行对结果进行编号吗？

例如

SELECT *,
    users::numeric/population::numeric*100 AS penetration,
    row_number() OVER(ORDER BY penetration DESC)
FROM states ORDER BY penetration DESC

产生一个错误：

ERROR: column "penetration" does not exist

原文

May i number results via row_number() OVER() by counted rows?

e.g.

SELECT *,
    users::numeric/population::numeric*100 AS penetration,
    row_number() OVER(ORDER BY penetration DESC)
FROM states ORDER BY penetration DESC

generates an error:

ERROR: column "penetration" does not exist

分享到QQ

分享到微博

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

发布评论

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

空气里的味道 2024-11-05 05:08:47

你不能在 postgresql 中这样做。在此处查找表达式：4.2.8。窗口函数调用，它说..它不能是输出列名称或数字..

您可以使用子查询：

SELECT states.*, penetration, row_number() OVER(ORDER BY penetration DESC)
  FROM states
  JOIN (SELECT id, users::numeric/population::numeric*100 AS penetration
          FROM states) s_pen on spen.id = states.id
 ORDER BY s_pen.penetration DESC

you can't do that in postgresql. Look for expression here:4.2.8. Window Function Calls, it says ..it cannot be output-column names or numbers..

You can user subquery:

SELECT states.*, penetration, row_number() OVER(ORDER BY penetration DESC)
  FROM states
  JOIN (SELECT id, users::numeric/population::numeric*100 AS penetration
          FROM states) s_pen on spen.id = states.id
 ORDER BY s_pen.penetration DESC

回复收藏 0 原文

扶醉桌前 2024-11-05 05:08:47

我不使用 postgre，但对于 t-sql 你必须这样做：

SELECT *,
    penetration,
    row_number() OVER(ORDER BY penetration DESC)
FROM 
(
   select users::numeric/population::numeric*100 AS penetration
   from states
)pen
 ORDER BY penetration DESC

I don't use postgre but for t-sql you have to do something like this:

SELECT *,
    penetration,
    row_number() OVER(ORDER BY penetration DESC)
FROM 
(
   select users::numeric/population::numeric*100 AS penetration
   from states
)pen
 ORDER BY penetration DESC

回复收藏 0 原文

~没有更多了~