H2 数据库 - Rank() 和 Row_Num() 的替代方案

发布于 2024-12-27 14:47:07 字数 435 浏览 1 评论 0原文

我在我的 java 应用程序中使用 H2 数据库。它似乎不支持 rank() 和 row_num() 函数，因为它们仍在其路线图列表

这是我尝试执行的查询

select * from (
   select *,
          rank() over(partition by MSISDN order by PORT_IN_DATE desc) rank 
   from TEST_PORTIN
) s 
where rank = 1 
  AND PORT_IN_DATE > '2012-01-16 23:20:27' 
ORDER BY PORT_IN_DATE

原文

I am using H2 database in my java application. It seems that it doesn't support rank() and row_num() functions as they are still on their roadmap list

Here's the query that I am trying to execute

select * from (
   select *,
          rank() over(partition by MSISDN order by PORT_IN_DATE desc) rank 
   from TEST_PORTIN
) s 
where rank = 1 
  AND PORT_IN_DATE > '2012-01-16 23:20:27' 
ORDER BY PORT_IN_DATE

分享到QQ

分享到微博

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

发布评论

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

彻夜缠绵 2025-01-03 14:47:07

您的特定查询不需要RANK()。您可以按如下方式重写它：

SELECT *
FROM TEST_PORTIN t1
WHERE PORT_IN_DATE > '2012-01-16 23:20:27'
AND NOT EXISTS (
    SELECT *
    FROM TEST_PORTIN t2
    WHERE t1.MSISDN = t2.MSISDN -- PARTITION BY clause translation
    AND t1.PORT_IN_DATE > t2.PORT_IN_DATE -- ORDER BY clause translation
)
ORDER BY PORT_IN_DATE

或者，使用量化比较谓词，更花哨一点（但不一定更快）

SELECT *
FROM TEST_PORTIN t1
WHERE PORT_IN_DATE > '2012-01-16 23:20:27'
AND PORT_IN_DATE >= ALL (
    SELECT PORT_IN_DATE
    FROM TEST_PORTIN t2
    WHERE t1.MSISDN = t2.MSISDN
)
ORDER BY PORT_IN_DATE

You don't need RANK() for your particular query. You can rewrite it as follows:

SELECT *
FROM TEST_PORTIN t1
WHERE PORT_IN_DATE > '2012-01-16 23:20:27'
AND NOT EXISTS (
    SELECT *
    FROM TEST_PORTIN t2
    WHERE t1.MSISDN = t2.MSISDN -- PARTITION BY clause translation
    AND t1.PORT_IN_DATE > t2.PORT_IN_DATE -- ORDER BY clause translation
)
ORDER BY PORT_IN_DATE

Or, a bit fancier (but not necessarily faster), using a quantified comparison predicate

SELECT *
FROM TEST_PORTIN t1
WHERE PORT_IN_DATE > '2012-01-16 23:20:27'
AND PORT_IN_DATE >= ALL (
    SELECT PORT_IN_DATE
    FROM TEST_PORTIN t2
    WHERE t1.MSISDN = t2.MSISDN
)
ORDER BY PORT_IN_DATE

回复收藏 0 原文

~没有更多了~