PARTITION BY在H2 db中不起作用 [英] PARTITION BY doesn't work in H2 db

问题描述

我正在使用PARTITION BY子句对结果进行排序.在此问题中提到了使用PARTITION BY的详细信息在多个列上按Sql Order by .当我在Oracle中运行时，它工作正常.我在单元测试用例中使用H2 db.当我在H2 db上运行相同的查询时，它不起作用. H2中的已知问题吗?是否有其他可以同时在Oracle和H2中使用的替代解决方案.

I'm using PARTITION BY clause to do sorting on the result. Details for using PARTITION BY is mentioned in this question Sql Order by on multiple column. It works fine when i run in Oracle. I'm using H2 db for my unit test cases. When i run the same query on H2 db, it doesn't work. Is it known issue in H2? Is there any alternate solution which can work in Oracle and H2 both.

推荐答案

我不认为H2支持窗口函数(又称解析函数).但是，您可以使用标准SQL在链接中进行查询:

I don't think H2 supports window functions (aka analytic functions). However, you can do the query in the link using standard SQL:

SELECT t.*
FROM yourtable t join
     (select vendorname, max(incidentdate) as maxdate
      from yourtable yt
      group by vendorname
     ) vn
     on vn.vendorname = yt.vendorname
ORDER BY vn.maxDate DESC, t.VendorName ASC, t.IncidentDate DESC;

尽管这应该在两种环境中都可以运行，但是over表单在SQL中的性能可能更好.

Although this should run in both environments, the over form probably performs better in SQL.

这篇关于PARTITION BY在H2 db中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！