我正在使用Postgresql版本9.1并查看
Postgres docs,我知道可以执行以下操作：
SELECT salary,sum(salary) OVER (ORDER BY salary) FROM empsalary;
这适用于我的查询.
现在我需要能够在OVER(ORDER BY …)中指定别名而不是列名？
我试过这个：
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_alias FROM empsalary;
问题更新后编辑
您无法在SELECT的同一级别引用列别名.你需要一个Sub-SELECT或CTE这样的东西.喜欢：
SELECT rank() OVER (ORDER BY result) AS rnk,result
FROM (
SELECT <compley expression> AS result
FROM tbl
WHERE <some condition>
GROUP BY id
) x;
试试这个问题：
SELECT rank() OVER (ORDER BY deltahdlcOverruns) AS rnk,y1_,deltahdlcOverruns
FROM (
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标准中保留.
