在PostgreSQL中的查询中使用窗口函数中的别名

概述我正在使用PostgreSQL版本9.1并查看 Postgres docs,我知道可以执行以下 *** 作： SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary; 这适用于我的查询. 现在我需要能够在OVER(ORDER BY …)中指定别名而不是列名？ 我试过这个： 编辑：我以前忘了将rank()添加到此查询： SELECT 我正在使用Postgresql版本9.1并查看 Postgres docs,我知道可以执行以下 *** 作：

SELECT salary,sum(salary) OVER (ORDER BY salary) FROM empsalary;

这适用于我的查询.

现在我需要能够在OVER(ORDER BY …)中指定别名而不是列名？

我试过这个：

编辑：我以前忘了将rank()添加到此查询：

SELECT salary,<complex Expression> as result,rank() OVER (ORDER BY result) FROM empsalary;

我收到一个错误,说列“结果”不存在.

是否可以在此处指定别名而不是列名？我错过了一些特殊的语法吗？

编辑：

我正在使用Hibernate,一些本机sql用于窗口功能.生成并执行的完整sql如下：

select        rank() OVER (ORDER BY deltahdlcOverruns DESC) as rank,this_.deviceNo as y1_,(SELECT _dev_.hdlcOverruns FROM abstractperformancestatistic _abs_         INNER JOIN enddevicestatistic _dev_ ON _dev_.ID = _abs_.ID         INNER JOIN linkstatistic _link_ ON _link_.ID = _dev_.linkStatistic_ID         INNER JOIN iptstatistic _ipt_ ON _ipt_.ID = _link_.iptStat_ID         WHERE this_.deviceNo=_dev_.deviceNo AND _abs_.dateTime <= '3910-06-07 00:00:00.0'        ORDER BY _abs_.dateTime DESC liMIT 1       )        -        (SELECT _dev_.hdlcOverruns FROM abstractperformancestatistic _abs_         INNER JOIN enddevicestatistic _dev_ ON _dev_.ID = _abs_.ID         INNER JOIN linkstatistic _link_ ON _link_.ID = _dev_.linkStatistic_ID         INNER JOIN iptstatistic _ipt_ ON _ipt_.ID = _link_.iptStat_ID         WHERE this_.deviceNo=_dev_.deviceNo AND _abs_.dateTime >= '3870-06-01 00:00:00.0'         ORDER BY _abs_.dateTime liMIT 1       )        AS deltahdlcOverruns from EndDeviceStatistic this_        inner join AbstractPerformanceStatistic this_1_ on this_.ID=this_1_.ID        inner join AbstractEntity this_2_ on this_.ID=this_2_.ID        left outer join RawEndDeviceStatistic this_3_ on this_.ID=this_3_.ID        left outer join linkStatistic l2_ on this_.linkStatistic_ID=l2_.ID        left outer join AbstractPerformanceStatistic l2_1_ on l2_.ID=l2_1_.ID        left outer join AbstractEntity l2_2_ on l2_.ID=l2_2_.ID        left outer join RawlinkStatistic l2_3_ on l2_.ID=l2_3_.ID        left outer join IPTStatistic i1_ on l2_.iptStat_ID=i1_.ID        left outer join AbstractPerformanceStatistic i1_1_ on i1_.ID=i1_1_.ID        left outer join AbstractEntity i1_2_ on i1_.ID=i1_2_.ID        left outer join RawIPTStatistic i1_3_ on i1_.ID=i1_3_.ID        where this_1_.dateTime between ? and ?        group by this_.deviceNo limit ?

将别名放在OVER子句后面.

SELECT salary,sum(salary) OVER (ORDER BY salary) AS my_aliasFROM   empsalary;

问题更新后编辑

您无法在SELECT的同一级别引用列别名.你需要一个Sub-SELECT或CTE这样的东西.喜欢：

SELECT rank() OVER (ORDER BY result) AS rnk,resultFROM  (    SELECT <compley Expression> AS result    FROM   tbl    WHERE  <some condition>    GROUP  BY ID    ) x;

试试这个问题：

SELECT rank() OVER (ORDER BY deltahdlcOverruns) AS rnk,y1_,deltahdlcOverrunsFROM  (    SELECT this_.deviceNo as y1_,(SELECT _dev_.hdlcOverruns FROM abstractperformancestatistic _abs_            JOIN   enddevicestatistic _dev_ USING (ID)           JOIN   linkstatistic _link_ ON _link_.ID = _dev_.linkStatistic_ID            JOIN   iptstatistic _ipt_ ON _ipt_.ID = _link_.iptStat_ID            WHERE  this_.deviceNo=_dev_.deviceNo AND _abs_.dateTime <= '3910-06-07 00:00:00.0'           ORDER  BY _abs_.dateTime DESC liMIT 1           )            -            (SELECT _dev_.hdlcOverruns FROM abstractperformancestatistic _abs_             JOIN   enddevicestatistic _dev_ USING (ID)            JOIN   linkstatistic _link_ ON _link_.ID = _dev_.linkStatistic_ID             JOIN   iptstatistic _ipt_ ON _ipt_.ID = _link_.iptStat_ID             WHERE  this_.deviceNo=_dev_.deviceNo AND _abs_.dateTime >= '3870-06-01 00:00:00.0'             ORDER  BY _abs_.dateTime liMIT 1           ) AS deltahdlcOverruns    FROM   EndDeviceStatistic this_     JOIN   AbstractPerformanceStatistic this_1_ USING (ID)    JOIN   AbstractEntity this_2_ USING (ID)    left   JOIN RawEndDeviceStatistic this_3_ USING (ID)    left   JOIN linkStatistic l2_ ON this_.linkStatistic_ID = l2_.ID     left   JOIN AbstractPerformanceStatistic l2_1_ ON l2_.ID=l2_1_.ID     left   JOIN AbstractEntity l2_2_ ON l2_.ID=l2_2_.ID     left   JOIN RawlinkStatistic l2_3_ ON l2_.ID=l2_3_.ID     left   JOIN IPTStatistic i1_ ON l2_.iptStat_ID=i1_.ID     left   JOIN AbstractPerformanceStatistic i1_1_ ON i1_.ID=i1_1_.ID     left   JOIN AbstractEntity i1_2_ ON i1_.ID=i1_2_.ID     left   JOIN RawIPTStatistic i1_3_ ON i1_.ID=i1_3_.ID     WHERE  this_1_.dateTime between ? and ?     GROUP  BY this_.deviceNo    liMIT  ?) x

我做了一些额外的语法简化.

在旁注：
不要像列名一样使用reserved words.虽然在Postgresql中允许特别排名,但它在sql：2003和sql：2008标准中保留.

总结

以上是内存溢出为你收集整理的在PostgreSQL中的查询中使用窗口函数中的别名全部内容，希望文章能够帮你解决在PostgreSQL中的查询中使用窗口函数中的别名所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。