时间戳

首页 > 解决方案 > 在Oracle中以逗号分隔的列中获取唯一长度的可用数据

sql - 在Oracle中以逗号分隔的列中获取唯一长度的可用数据

问题描述

需要获取以逗号分隔的列中可用数据的唯一长度。已尝试以下查询，但收到错误提示

ORA-00936: missing expression 00936. 00000 - "missing expression" *Cause: *Action: Error at Line: 2 Column: 16

使用的查询

SELECT LISTAGG(
           SELECT DISTINCT LENGTH(ColumnName)
           FROM TableName
           WHERE LENGTH(ColumnName) NOT BETWEEN 6 AND 10) AS AliasName, ','
           ) within group (order by ColumnName) 
FROM DUAL;

下面的查询错误说

ORA-30482: DISTINCT option not allowed for this function 30482. 00000 - "DISTINCT option not allowed for this function" *Cause: *Action: Error at Line: 1 Column: 8

SELECT LISTAGG(DISTINCT LENGTH(ColumnName), ',')  within group (order by LENGTH(ColumnName))
FROM TableName
WHERE LENGTH(ColumnName) NOT BETWEEN 6 AND 10;

请帮助我。

标签： sqloraclesplitoracle11g

解决方案

以下查询提供所需的结果。

SELECT listagg(AliasName, ',') within group (order by AliasName) AS NewAliasName 
FROM (
    SELECT DISTINCT LENGTH(ColumnName) AS AliasName
    FROM TableName
    WHERE LENGTH(ColumnName) NOT BETWEEN 6 AND 10
    );

我能得到比这更好的查询吗？

推荐阅读