我想触发中建议的查询https://stackoverflow.com/a/3800572/2968357 https://stackoverflow.com/a/3800572/2968357在 HSQLDB 数据库上使用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: )
meaning 分区依据不支持。
我对 HSQLDB 的特定查询有解决方法吗?
HSQLDB 支持该答案中的第二个查询。如果您使用 HSQLDB DatabaseManager 和insert test data
从它的options
菜单中,您将获得填充有数据且适合此类查询的表。
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 中有一个有趣的替代方案,它的性能非常好:
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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)