使用Tableau Java HyperAPI删除.hyper文件首尾行报错，寻求正确实现方案

错误原因 #

你尝试的两种写法都不符合Hyper SQL的语法规范：

• 第一种的TOP(1)是SQL Server专属语法，Hyper不支持
• 第二种的ROWNUM是Oracle专属语法，Hyper也不支持

正确实现方案 #

首先需要明确：关系型表本身没有默认的「首行」「末行」概念，你需要指定排序规则来确定哪一行是需要删除的目标行，以下两种方案均可实现需求：

方案1：基于唯一标识列排序（推荐，性能最高） #

假设你的表有主键/唯一标识列id，按该列升序排序后的第一条为首行，降序排序后的第一条为末行，实现代码如下：

删除首行

long rowCountDeletedFirst = connection.executeCommand(
        "DELETE FROM " + escapeName("Customer") +
        " WHERE " + escapeName("id") + " = (" +
        "SELECT " + escapeName("id") + " FROM " + escapeName("Customer") +
        " ORDER BY " + escapeName("id") + " ASC LIMIT 1"
        + ")"
).getAsLong();

删除末行

long rowCountDeletedLast = connection.executeCommand(
        "DELETE FROM " + escapeName("Customer") +
        " WHERE " + escapeName("id") + " = (" +
        "SELECT " + escapeName("id") + " FROM " + escapeName("Customer") +
        " ORDER BY " + escapeName("id") + " DESC LIMIT 1"
        + ")"
).getAsLong();

方案2：无唯一标识列时使用窗口函数 #

如果你的表没有可用于排序的唯一列，可以用row_number()窗口函数生成行号后删除：

// 替换下方的col1、col2、col3为你的表实际的所有列名
String allColumns = String.join(",", new String[]{escapeName("col1"), escapeName("col2"), escapeName("col3")});
// 删除首行（按默认顺序生成行号）
long rowCountDeletedFirst = connection.executeCommand(
        "WITH numbered_rows AS (" +
        "SELECT *, row_number() OVER () AS rn FROM " + escapeName("Customer") +
        ") " +
        "DELETE FROM " + escapeName("Customer") +
        " WHERE (" + allColumns + ") IN (" +
        "SELECT " + allColumns + " FROM numbered_rows WHERE rn = 1"
        + ")"
).getAsLong();

// 删除末行只需将行号过滤条件改为对应总行数即可
long totalRows = connection.executeQuery("SELECT COUNT(*) FROM " + escapeName("Customer")).next().getLong(0);
long rowCountDeletedLast = connection.executeCommand(
        "WITH numbered_rows AS (" +
        "SELECT *, row_number() OVER () AS rn FROM " + escapeName("Customer") +
        ") " +
        "DELETE FROM " + escapeName("Customer") +
        " WHERE (" + allColumns + ") IN (" +
        "SELECT " + allColumns + " FROM numbered_rows WHERE rn = " + totalRows
        + ")"
).getAsLong();

内容的提问来源于stack exchange，提问作者Farid Arshad