行中的列值 [英] column values in a row

问题描述

我有下表

    id    count   hour   age   range
    -------------------------------------
    0       5       10     61     10-200
    1       6       20     61     10-200
    2       7       15     61     10-200  
    5       9       5      61     201-300
    7       10      25     61     201-300
    0       5       10     62     10-20
    1       6       20     62     10-20
    2       7       15     62     10-20  
    5       9       5      62     21-30
    1       8       6      62     21-30
    7       10      25     62     21-30
    10      15      30     62     31-40

我需要选择不同的列范围值 我尝试了以下查询

I need to select distinct values of column range I tried following query

Select distinct range as interval from table name where age  = 62;

其结果在以下列中:

interval
----------
10-20
21-30
31-41

如何获得如下结果?

10-20, 21-30, 31-40

我现在正在尝试以下查询:

EDITED: I am now trying following query:

select sys_connect_by_path(range,',') interval
from
    (select distinct NVL(range,'0') range , ROW_NUMBER() OVER (ORDER BY RANGE) rn 

 from table_name where age = 62)

 where connect_by_isleaf = 1 CONNECT BY rn = PRIOR rn+1 start with rn = 1;

哪个给我的输出为:

Interval
----------------------------------------------------------------------------
, 10-20,10-20,10-20,21-30,21-30, 31-40

guys plz帮助我获得所需的输出.

guys plz help me to get my desired output.

推荐答案

如果您使用的是11.2而不是11.1，则可以使用LISTAGG聚合函数

If you are on 11.2 rather than just 11.1, you can use the LISTAGG aggregate function

SELECT listagg( interval, ',' ) 
         WITHIN GROUP( ORDER BY interval )
  FROM (SELECT DISTINCT range AS interval
          FROM table_name
         WHERE age = 62)

如果您使用的是Oracle的早期版本，则可以使用另一个 Oracle字符串聚合技术.在11.2之前，我个人倾向于创建一个用户定义的聚合函数，这样您就可以

If you are using an earlier version of Oracle, you could use one of the other Oracle string aggregation techniques on Tim Hall's page. Prior to 11.2, my personal preference would be to create a user-defined aggregate function so that you can then

SELECT string_agg( interval )
  FROM (SELECT DISTINCT range AS interval
              FROM table_name
             WHERE age = 62)

但是，如果您不想创建函数，则可以使用

If you don't want to create a function, however, you can use the ROW_NUMBER and SYS_CONNECT_BY_PATH approach though that tends to get a bit harder to follow

with x as (
  SELECT DISTINCT range AS interval
          FROM table_name
         WHERE age = 62 )
select ltrim( max( sys_connect_by_path(interval, ','))
                keep (dense_rank last order by curr),
              ',') range
  from (select interval,
               row_number() over (order by interval) as curr,
               row_number() over (order by interval) -1 as prev
          from x)
connect by prev = PRIOR curr
  start with curr = 1

这篇关于行中的列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！