在Oracle中总结空行

Question

我有这样的数据集：

+---------------+-------+
| SAMPLE_NUMBER | SCORE |
+---------------+-------+
|             1 | 100   |
|             2 | 97    |
|             3 | 124   |
|             4 | 762   |
|             5 | 999   |
|             6 | 1200  |
|             7 | NULL  |
|             8 | NULL  |
|             9 | NULL  |
|            10 | NULL  |
+---------------+-------+

我希望能够总结空行，而不是全部显示它们。因此，理想情况下，我希望上面的内容是这样的：

+---------------+-------+
| SAMPLE_NUMBER | SCORE |
+---------------+-------+
| 1             | 100   |
| 2             | 97    |
| 3             | 124   |
| 4             | 762   |
| 5             | 999   |
| 6             | 1200  |
| 7-10          | NULL  |
+---------------+-------+

甲骨文有什么办法做到这一点吗？或者这是我在查询后必须做的事情？

Answer 1

我的猜测是，这应该是表示层的一部分，因为您必须将sample_number转换为字符串(假设它是数字类型)。您的需求的另一种选择是返回最小和最大的连续sample_number：

with t (SAMPLE_NUMBER, SCORE) as (
    values  (1, 100)
        ,   (2, 97)
        ,   (3, 124)
        ,   (4, 762)
        ,   (5, 999)
        ,   (6, 1200)
        ,   (7, NULL)
        ,   (8, NULL)
        ,   (9, NULL)
        ,   (10, NULL)
)
select min(sample_number), max(sample_number), grp, score
from (
    select SAMPLE_NUMBER, SCORE
       ,   row_number() over (order by SAMPLE_NUMBER)
       -   row_number() over (partition by SCORE
                              order by SAMPLE_NUMBER) as grp
    from t
) group by grp, score
order by grp;

1           2           GRP                  SCORE      
----------- ----------- -------------------- -----------
      1           1                    0         100
      2           2                    1          97
      3           3                    2         124
      4           4                    3         762
      5           5                    4         999
      6           6                    5        1200
      7          10                    6           -

尝试针对db2，所以您可能需要稍微调整它。

编辑:当得分不是null时，将行视为个人。

with t (SAMPLE_NUMBER, SCORE) as (
    values  (1, 100)
        ,   (2, 97)
        ,   (3, 97)
        ,   (4, 762)
        ,   (5, 999)
        ,   (6, 1200)
        ,   (7, NULL)
        ,   (8, NULL)
        ,   (9, NULL)
        ,   (10, NULL)
)
select min(sample_number), max(sample_number), grp, score
from (
    select SAMPLE_NUMBER, SCORE
       ,   row_number() over (order by SAMPLE_NUMBER)
       -   row_number() over (partition by SCORE
                              order by SAMPLE_NUMBER) as grp
    from t
) group by grp, score
         , case when score is not null then sample_number end
order by grp;

1           2           GRP                  SCORE      
----------- ----------- -------------------- -----------
      1           1                    0         100
      2           2                    1          97
      3           3                    1          97
      4           4                    3         762
      5           5                    4         999
      6           6                    5        1200
      7          10                    6           -

您可能希望将max映射为null，以防它与min相同：

[...]
select min(sample_number)
     , nullif(max(sample_number), min(sample_number))
     , grp
     , score
from ...

1           2           GRP                  SCORE      
----------- ----------- -------------------- -----------
      1           -                    0         100
      2           -                    1          97
      3           -                    1          97
      4           -                    3         762
      5           -                    4         999
      6           -                    5        1200
      7          10                    6           -

Answer 2

是。对于您的样本数据：

select (case when score is null then min(sample_number) || '-' || max(sample_number)
             else min(sample_number)
        end) as sample_number,
       score
from table t
group by score
order by min(id)

换句话说，group by score，然后摆弄样本编号。注意:这假设你没有重复的分数。如果您这样做，您可以使用一个更复杂的版本：

select (case when score is null then min(sample_number) || '-' || max(sample_number)
             else min(sample_number)
        end) as sample_number,
       score
from (select t.*,
             row_number() over (partition by score order by sample_number) as seqnum
      from table t
     ) t
group by score, (case when score is not null then seqnum end);

Answer 3

SELECT DISTINCT 
    DECODE(SCORE
          ,NULL
          ,(SELECT COUNT()+1 
            FROM TAB_NAME 
            WHERE SCORE IS NOT NULL)
    || '-'
    || (SELECT COUNT() 
        FROM TAB_NAME)
          ,SAMPLE_NUMBER) NUM
   , NVL(TO_CHAR(SCORE),'NULL') SCRE
FROM TAB_NAME
ORDER BY 1 ASC;