H2数据库SQL查询报错42001-197求排查及优化方案

我不是SQL和H2数据库专家，现有ORDERS和PRODUCTS两张表，想查询各年度销量前三的商品分类，执行了以下SQL语句：

WITH year_categories AS ( 
    SELECT EXTRACT(YEAR FROM ORDERS.CREATED_AT) AS year, 
    PRODUCTS.CATEGORY AS "Category", 
    SUM(ORDERS.TOTAL) AS total_sales 
    FROM ORDERS JOIN PRODUCTS 
    ON ORDERS.PRODUCT_ID = PRODUCTS.ID 
    GROUP BY year, PRODUCTS.CATEGORY 
) 
SELECT year, "Category", total_sales 
FROM ( 
    SELECT year, "Category", total_sales, 
    ROW_NUMBER() OVER (PARTITION BY year ORDER BY total_sales DESC) as row_num
    FROM year_categories 
) sub 
WHERE sub.row_num <= 3 
ORDER BY year, total_sales DESC;

出现的错误信息：

Syntax error in SQL statement " WITH YEAR_CATEGORIES AS ( SELECT EXTRACT(YEAR FROM ORDERS.CREATED_AT) AS YEAR, PRODUCTS.CATEGORY AS ""Category"", SUM(ORDERS.TOTAL) AS TOTAL_SALES FROM ORDERS JOIN PRODUCTS ON ORDERS.PRODUCT_ID = PRODUCTS.ID GROUP BY YEAR, PRODUCTS.CATEGORY ) SELECT YEAR, ""Category"", TOTAL_SALES FROM ( SELECT YEAR, ""Category"", TOTAL_SALES, ROW_NUMBER() OVER (PARTITION[*] BY YEAR ORDER BY TOTAL_SALES DESC) AS ROW_NUM FROM YEAR_CATEGORIES ) SUB WHERE SUB.ROW_NUM <= 3 ORDER BY YEAR, TOTAL_SALES DESC; "; expected ")"; SQL statement: WITH year_categories AS ( SELECT EXTRACT(YEAR FROM ORDERS.CREATED_AT) AS year, PRODUCTS.CATEGORY AS "Category", SUM(ORDERS.TOTAL) AS total_sales FROM ORDERS JOIN PRODUCTS ON ORDERS.PRODUCT_ID = PRODUCTS.ID GROUP BY year, PRODUCTS.CATEGORY ) SELECT year, "Category", total_sales FROM ( SELECT year, "Category", total_sales, ROW_NUMBER() OVER (PARTITION BY year ORDER BY total_sales DESC) as row_num FROM year_categories ) sub WHERE sub.row_num <= 3 ORDER BY year, total_sales DESC; [42001-197]

错误原因 #

你使用的H2数据库版本（1.4.197）对标准SQL的窗口函数支持不完善，不认可ROW_NUMBER() OVER (PARTITION BY ...)的语法结构，这是触发语法错误的核心原因。

解决方案 #

方案一：升级H2版本 #

直接将H2数据库升级到2.x及以上版本，新版本完全兼容标准SQL的窗口函数和CTE（WITH子句）语法，你原来的SQL语句可以直接正常运行。

方案二：不升级版本的替代写法 #

如果无法升级数据库，可以改用子查询关联的方式实现需求，SQL语句如下：

SELECT
    yc.year,
    yc."Category",
    yc.total_sales
FROM (
    SELECT
        EXTRACT(YEAR FROM o.CREATED_AT) AS year,
        p.CATEGORY AS "Category",
        SUM(o.TOTAL) AS total_sales
    FROM ORDERS o
    JOIN PRODUCTS p ON o.PRODUCT_ID = p.ID
    GROUP BY EXTRACT(YEAR FROM o.CREATED_AT), p.CATEGORY
) yc
WHERE (
    SELECT COUNT(*)
    FROM (
        SELECT
            EXTRACT(YEAR FROM o2.CREATED_AT) AS year,
            p2.CATEGORY AS "Category",
            SUM(o2.TOTAL) AS total_sales
        FROM ORDERS o2
        JOIN PRODUCTS p2 ON o2.PRODUCT_ID = p2.ID
        GROUP BY EXTRACT(YEAR FROM o2.CREATED_AT), p2.CATEGORY
    ) yc2
    WHERE yc2.year = yc.year
    AND yc2.total_sales >= yc.total_sales
) <= 3
ORDER BY yc.year, yc.total_sales DESC;

这个查询的逻辑是：先统计每个年度各商品分类的总销量，再针对每一条统计结果，计算同年度内销量大于等于它的分类数量，只要数量不超过3，就保留该记录，以此筛选出年度销量前三的分类。

注意：如果存在多个分类销量并列的情况，这个写法会把所有并列的分类都包含进来（比如4个分类并列第三，都会被选出）。如果你需要和原SQL逻辑完全一致（并列时只取部分，用行号区分），可以在子查询的统计部分添加额外排序条件，比如p.CATEGORY，确保排序唯一。

内容的提问来源于stack exchange，提问作者Doctor DIRE