如何在HSQLDB中查找顺序编号方面的差距？(How to find gaps in sequential numbering in HSQLDB?)

如何在HSQLDB中查找顺序编号方面的差距？(How to find gaps in sequential numbering in HSQLDB?)

我试图将这个针对MySQL的答案改写为HSQLDB（2.4.0，通过Hibernate 5.2.12）。 我已经用“CASE WHEN”调用替换MySQL“IF”函数，如下所示：

SELECT CONCAT(z.expected, CASE WHEN z.got-1>z.expected THEN CONCAT(' thru ',z.got-1) ELSE '' END) AS missing FROM ( SELECT @rownum\\:=@rownum+1 AS expected, CASE WHEN @rownum=id THEN 0 ELSE @rownum\\:=id END AS got FROM (SELECT @rownum\\:=0) AS a JOIN Notices WHERE fonds_cote = '1Fi' ORDER BY id ) AS z WHERE z.got!=0;

该:被转义以避免来自Hibernate的解析错误。 不过，它不工作，我面对：

org.hsqldb.HsqlException: unexpected token: ( at org.hsqldb.error.Error.parseError(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.ParserBase.unexpectedToken(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.ParserDQL.XreadSelect(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.ParserCommand.compilePart(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.ParserCommand.compileStatement(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.Session.compileStatement(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.StatementManager.compile(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.Session.execute(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146) ~[hibernate-core-5.2.12.Final.jar:5.2.12.Final]

什么是正确的语法？ 或者在HSQLDB中有更好的方法吗？

I'm trying to adapt this answer made for MySQL to HSQLDB (2.4.0, through Hibernate 5.2.12). I have replaced MySQL "IF" function by "CASE WHEN" calls as follows:

SELECT CONCAT(z.expected, CASE WHEN z.got-1>z.expected THEN CONCAT(' thru ',z.got-1) ELSE '' END) AS missing FROM ( SELECT @rownum\\:=@rownum+1 AS expected, CASE WHEN @rownum=id THEN 0 ELSE @rownum\\:=id END AS got FROM (SELECT @rownum\\:=0) AS a JOIN Notices WHERE fonds_cote = '1Fi' ORDER BY id ) AS z WHERE z.got!=0;

The : are escaped to avoid a parsing error from Hibernate. Still, it does not work and I face:

org.hsqldb.HsqlException: unexpected token: ( at org.hsqldb.error.Error.parseError(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.ParserBase.unexpectedToken(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.ParserDQL.XreadSelect(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.ParserCommand.compilePart(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.ParserCommand.compileStatement(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.Session.compileStatement(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.StatementManager.compile(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.Session.execute(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source) ~[hsqldb-2.4.0.jar:2.4.0] at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146) ~[hibernate-core-5.2.12.Final.jar:5.2.12.Final]

What is the correct syntax? Or is there a better way to do it in HSQLDB?

最满意答案

问题是使用变量@rownum 。 这不受HSQLDB支持。

使用HSQLDB，您可以以简单的方式进行操作。

假设该表称为CUSTOMER，序列列称为ID。 下面的查询显示了SEQUENCE_ARRAY是如何工作的并且用于查找缺失的值。

-- this returns consecutive numbers within a fixed range SELECT * FROM UNNEST (SEQUENCE_ARRAY(1, 1000, 1)) -- this returns all the possible consecutive numbers for an existing table SELECT * FROM UNNEST (SEQUENCE_ARRAY((SELECT MIN(ID) FROM CUSTOMER), (SELECT MAX(ID) FROM CUSTOMER), 1)) -- this returns the list of unused IDs. SELECT * FROM UNNEST (SEQUENCE_ARRAY((SELECT MIN(ID) FROM CUSTOMER), (SELECT MAX(ID) FROM CUSTOMER), 1)) SEQ(IDCOL) LEFT OUTER JOIN CUSTOMER ON CUSTOMER.ID = SEQ.IDCOL WHERE CUSTOMER.ID IS NULL

The problem is the use of the variable @rownum. This is not supported by HSQLDB.

With HSQLDB you can do it in a simple manner.

Suppose the table is called CUSTOMER and the sequence column is called ID. The queries below show how SEQUENCE_ARRAY works and is used for finding the missing values.

-- this returns consecutive numbers within a fixed range SELECT * FROM UNNEST (SEQUENCE_ARRAY(1, 1000, 1)) -- this returns all the possible consecutive numbers for an existing table SELECT * FROM UNNEST (SEQUENCE_ARRAY((SELECT MIN(ID) FROM CUSTOMER), (SELECT MAX(ID) FROM CUSTOMER), 1)) -- this returns the list of unused IDs. SELECT * FROM UNNEST (SEQUENCE_ARRAY((SELECT MIN(ID) FROM CUSTOMER), (SELECT MAX(ID) FROM CUSTOMER), 1)) SEQ(IDCOL) LEFT OUTER JOIN CUSTOMER ON CUSTOMER.ID = SEQ.IDCOL WHERE CUSTOMER.ID IS NULL