在oracle中使用varchar字段时出现错误

Question

我有一个表，id和value都是varchars。

>> id          "value"
-----------------------
   a             1
   a             2
   a             5
   a             6
   a             7
   a             8
   b             4
   b             5
   b             6
   b             9

我只想看看输出

value    Result
-----------------
a        1-2,5,6-8
b        4-6,9

我试过使用ListAGG()函数

它将错误“值”作为无效标识符。

SELECT
      LISTAGG ( TXT,
              ', ' )
      WITHIN GROUP (ORDER BY "value")
          "Result"
FROM
      (SELECT
            CASE
                WHEN MIN ( "value" ) = MAX ( "value" )
                THEN
                    CAST ( MIN ( "value" ) AS VARCHAR2 ( 400 ) )
                ELSE
                       MIN ( "value" )
                    || '-'
                    || MAX ( "value" )
            END
                AS TXT,
            MIN ( "value" ) AS SOURCE
       FROM
            (SELECT
                   ROW_NUMBER ( ) OVER (ORDER BY MIN ( "value" ))
                   - TO_NUMBER ( "value" )
                       AS GRP
             FROM
                   TABL2)
       GROUP BY
            GRP);

我想有什么类型错配吗？你能帮帮我吗..。

Answer 1

代码有很多问题。但是，错误的原因是在子查询的选择中没有包括value。

我还注意到您缺少PARTITION BY id，这是完成所需分组所必需的。您也没有在外部查询中按id进行分组。

此查询：

SELECT id,
      LISTAGG ( TXT,', ' )
      WITHIN GROUP (ORDER BY source)"Result"
FROM
      (SELECT id,
            CASE
                WHEN MIN ( "value" ) = MAX ( "value" )
                THEN
                    CAST ( MIN ( "value" ) AS VARCHAR2 ( 400 ) )
                ELSE
                       MIN ( "value" )
                    || '-'
                    || MAX ( "value" )
            END
                AS TXT,
            MIN ( "value" ) AS SOURCE
       FROM (
            SELECT id,"value",
                   ROW_NUMBER ( )  OVER (partition by id ORDER BY MIN ( "value" ))
                   - TO_NUMBER ( "value" )
                       AS GRP
             FROM
                   TABL2
             GROUP BY id,"value"
       )
       GROUP BY
            id,GRP)
GROUP BY id

生产：

a   1-2, 5-8
b   4-6, 9

SQLFiddle在这里