sql - 如何在 H2 中交叉连接两个选择

Question

我正在尝试在 H2 数据库中交叉连接两个简单的查询结果：

SELECT *
FROM
       (
              SELECT DISTINCT CATEGORY
              FROM GENERIC_RECORD
       ) 
       CROSS JOIN
       (    
              SELECT CAST( MONTH(LOCAL_DATE) AS VARCHAR(2)) || '-' || CAST(YEAR(LOCAL_DATE) AS VARCHAR(4)) AS DATE
              FROM GENERIC_RECORD 
          GROUP BY CAST( MONTH(LOCAL_DATE) AS VARCHAR(2)) || '-' || CAST(YEAR(LOCAL_DATE) AS VARCHAR(4))
       )

但我得到了错误：

Syntax error in SQL statement "SELECT
    ((CAST(MONTH(""LOCAL_DATE"") AS VARCHAR(2)) || '-') || CAST(YEAR(""LOCAL_DATE"") AS VARCHAR(4))) AS ""DATE""
FROM ""PUBLIC"".""GENERIC_RECORD""
GROUP BY (CAST(MONTH(""LOCAL_DATE"") AS VARCHAR(2)) || '-') ||[*] CAST(YEAR(""LOCAL_DATE"") AS VARCHAR(4))"; SQL statement:
CREATE FORCE VIEW (
    SELECT
        ((CAST(MONTH("LOCAL_DATE") AS VARCHAR(2)) || '-') || CAST(YEAR("LOCAL_DATE") AS VARCHAR(4))) AS "DATE"
    FROM "PUBLIC"."GENERIC_RECORD"
    GROUP BY (CAST(MONTH("LOCAL_DATE") AS VARCHAR(2)) || '-') || CAST(YEAR("LOCAL_DATE") AS VARCHAR(4))
) AS
SELECT
    ((CAST(MONTH("LOCAL_DATE") AS VARCHAR(2)) || '-') || CAST(YEAR("LOCAL_DATE") AS VARCHAR(4))) AS "DATE"
FROM "PUBLIC"."GENERIC_RECORD"
GROUP BY (CAST(MONTH("LOCAL_DATE") AS VARCHAR(2)) || '-') || CAST(YEAR("LOCAL_DATE") AS VARCHAR(4)) [42000-200] 42000/42000 (Help)

单个嵌套的 SELECT 工作得很好，但在加入时就不行了。

我想获得一个包含 {CATEGORY, DATE} 列的表格，其中每个不同的类别都链接到每个月

标签： sqlh2