HSQLDB中的PARTITION BY替代 [英] PARTITION BY alternative in HSQLDB

问题描述

我想触发 https://stackoverflow.com/a/3800572/2968357 中建议的查询使用 select * (例如

I would like to fire the query suggested in https://stackoverflow.com/a/3800572/2968357 on a HSQLDB database using select * such as

WITH tmpTable AS (
SELECT p.* ,
        ROW_NUMBER()  OVER(PARTITION BY p.groupColumn order by p.groupColumn desc) AS rowCount
  FROM sourceTable p) SELECT * FROM  tmpTable WHERE tmpTable.rowCount = 1

但出现以下错误:

Caused by: org.hsqldb.HsqlException: unexpected token: PARTITION required: )

不支持 PARTITION BY .

我在HSQLDB上的特定查询是否有解决方法?

Is there a work-around for my specific query on HSQLDB?

推荐答案

HSQLDB支持该答案中的第二个查询.如果使用HSQLDB DatabaseManager及其options菜单中的insert test data，则将获得填充有数据且适用于此类查询的表.

The second query in that answer is supported by HSQLDB. If you use the HSQLDB DatabaseManager and the insert test data from its options menu, you get tables that are populated with data and are suitable for this type of query.

SELECT MIN(x.id),  
     x.customerID, 
     x.total
FROM INVOICE x
JOIN (SELECT p.customerID,
             MAX(total) AS max_total
        FROM INVOICE p
    GROUP BY p.customerID) y ON y.customerID = x.customerID
                          AND y.max_total = x.total
GROUP BY x.customerID, x.total

但是，当您要从两个表中进行选择时，HSQLDB中有一个有趣的替代方案，它的性能很好:

But when you want to select from two tables, there is an interesting alternative in HSQLDB that performs quite well:

SELECT INV.* FROM CUSTOMER, 
LATERAL (SELECT ID, CUSTOMERID, CUSTOMER.FIRSTNAME, TOTAL 
FROM INVOICE  
WHERE CUSTOMERID = CUSTOMER.ID 
ORDER BY TOTAL, ID LIMIT 1) INV

此查询返回如下结果:

ID CUSTOMERID FIRSTNAME TOTAL   
-- ---------- --------- ------- 
1  0          Laura     2700.90 
36 1          Robert    4761.60 
27 3          Michael   3420.30 
12 4          Bill      3867.30 

这篇关于HSQLDB中的PARTITION BY替代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！