如何在Oracle中的记录范围内选择范围 [英] How to select ranges in a range of record in oracle

问题描述

如果我有这样的桌子

Number Status
------ ------
1      A
2      A
3      A
4      U
5      U
6      A
7      U
8      U
9      A
10     A

我可以使用哪个查询将范围分为状态= A的范围?

What query can I use to group the range into ranges where Status = A?

Range  Count  Status
-----  -----  ------
1-3    3      A
6-6    1      A
9-10   2      A

我的查询是

select min(number) || '--' || max(number), count(*), Status
from table
where Status = 'A'
group by Status

Range  Count  Status
-----  -----  ------
1-10   6      A 

推荐答案

这是一种不错的方法，名称为"塔比比松方法"由Aketi Jyuuzou提供.

This is a nice way, fancy name "Tabibitosan method" given by Aketi Jyuuzou.

SQL> WITH data AS
  2    (SELECT num - DENSE_RANK() OVER(PARTITION BY status ORDER BY num) grp,
  3      status,
  4      num
  5    FROM t
  6    )
  7  SELECT MIN(num)
  8    ||' - '
  9    || MAX(num) range,
 10    COUNT(*) cnt
 11  FROM data
 12  WHERE status='A'
 13  GROUP BY grp
 14  ORDER BY grp
 15  /

RANGE         CNT
------ ----------
1 - 3           3
6 - 6           1
9 - 10          2

SQL>

注意:最好使用DENSE_RANK避免重复.

Note It is better to use DENSE_RANK to avoid duplicates.

表格

SQL> SELECT * FROM t ORDER BY num;

       NUM S
---------- -
         1 A
         1 A
         2 A
         2 A
         3 A
         4 U
         5 U
         6 A
         7 U
         8 U
         9 A

       NUM S
---------- -
        10 A

12 rows selected.

num = 1重复.

There are duplicates for num = 1.

使用 DENSE_RANK :

SQL> WITH data AS
  2    (SELECT num - DENSE_RANK() OVER(PARTITION BY status ORDER BY num) grp,
  3      status,
  4      num
  5    FROM t
  6    )
  7  SELECT MIN(num)
  8    ||' - '
  9    || MAX(num) range,
 10    COUNT(*) cnt
 11  FROM data
 12  WHERE status='A'
 13  GROUP BY grp
 14  ORDER BY grp
 15  /

RANGE         CNT
------ ----------
1 - 3           5
6 - 6           1
9 - 10          2

SQL>

使用 ROW_NUMBER :

SQL> WITH DATA AS
  2    (SELECT num - ROW_NUMBER() OVER(PARTITION BY status ORDER BY num) grp,
  3      status,
  4      num
  5    FROM t
  6    )
  7  SELECT MIN(num)
  8    ||' - '
  9    || MAX(num) range,
 10    COUNT(*) cnt
 11  FROM data
 12  WHERE status='A'
 13  GROUP BY grp
 14  ORDER BY grp
 15  /

RANGE         CNT
------ ----------
2 - 3           2
1 - 2           2
1 - 6           2
9 - 10          2

SQL>

因此，如果重复，则 ROW_NUMBER 查询将给出错误的结果.您应该使用 DENSE_RANK .

So, in case of duplicates, the ROW_NUMBER query would give incorrect results. You should use DENSE_RANK.

这篇关于如何在Oracle中的记录范围内选择范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！