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

Question

我在我的 Java 应用程序中使用 H2 数据库。好像不支持rank()和 row_num()功能，因为他们仍然在他们的 roadmap list

这是我试图执行的查询

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

Answer 1

你不需要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