超难写的试图,望高手指教

Question

有如下一张表,XH和MC分别为字段名
XH MC
1 AAA
2 AAA
3 AAA
4 BBB
5 BBB
6 BBB
7 AAA
8 AAA
9 AAA
10 CCC
11 CCC
12 BBB
13 BBB想实现下面结果的试图,也就是按MC字段进行分段,
MIN-XH MAX-XH MC
1 3 AAA
4 6 BBB
7 9 AAA
10 11 CCC
12 13 BBB
望高手指教,急急急急急急急急急

Answer 1

是不是我想简单啦？create or replace view v1 
as
select min(xh) "MIN-XH",
       max(xh) "MAX-XH",
       mc      "MC"
from t
group by mc;

Answer 2

SQL> SELECT b.xh "min-xh",
  2         DECODE
  3            (b.xh,
  4             (SELECT MAX (xh)
  5                FROM (SELECT xh, mc
  6                        FROM (SELECT a.xh,
  7                                     DECODE (a.xh,
  8                                             1, a.mc,
  9                                             DECODE (a.mc,
 10                                                     (SELECT mc
 11                                                        FROM test1
 12                                                       WHERE xh = a.xh - 1),
'',
 13                                                     a.mc
 14                                                    )
 15                                            ) mc
 16                                FROM test1 a)
 17                       WHERE mc IS NOT NULL)), (SELECT MAX (xh)
 18                                                  FROM test1),
 19             (SELECT MIN (xh) - 1
 20                FROM (SELECT xh, mc
 21                        FROM (SELECT a.xh,
 22                                     DECODE (a.xh,
 23                                             1, a.mc,
 24                                             DECODE (a.mc,
 25                                                     (SELECT mc
 26                                                        FROM test1
 27                                                       WHERE xh = a.xh - 1),
'',
 28                                                     a.mc
 29                                                    )
 30                                            ) mc
 31                                FROM test1 a)
 32                       WHERE mc IS NOT NULL) c
 33               WHERE c.xh > b.xh)
 34            ) "max-xh",
 35         b.mc mc
 36    FROM (SELECT xh, mc
 37            FROM (SELECT a.xh,
 38                         DECODE (a.xh,
 39                                 1, a.mc,
 40                                 DECODE (a.mc, (SELECT mc
 41                                                  FROM test1
 42                                                 WHERE xh = a.xh - 1), '', a.
mc)
 43                                ) mc
 44                    FROM test1 a)
 45           WHERE mc IS NOT NULL) b;    min-xh     max-xh MC
---------- ---------- ----------
         1          3 aaa
         4          6 bbb
         7          9 aaa
        10         11 ccc
        12         13 bbbSQL>
写是写出来了，就是太麻烦了，谁给简化一下吧

Answer 3

SQL> select * from a;       ID1 ID2
---------- ----------
         1 aaa
         2 aaa
         3 aaa
         4 bbb
         5 bbb
         6 bbb
         7 aaa
         8 aaa
         9 aaa
        10 ccc
        11 ccc
        12 bbb
        13 bbbSQL> select max(id1) max_id,min(id1) min_id,id2
  2  from (select * from a order by id2,id1)
  3  group by id2,rownum - id1
  4  order by max_id
  5  /    MAX_ID     MIN_ID ID2
---------- ---------- ----------
         3          1 aaa
         6          4 bbb
         9          7 aaa
        11         10 ccc
        13         12 bbb

Answer 4

开始我也和 njhart2003()一样想简单了：）

Answer 5

嗯，真好啊。
还是不习惯用rownum啊

Answer 6

SQL> select min(xh) "MIN-XH",max(xh) "MAX-XH",mc
  2  from
  3  (select * from t order by mc,xh)
  4  group by mc,rownum-xh
  5  order by min(xh);    MIN-XH     MAX-XH MC
---------- ---------- ---
         1          3 AAA
         4          6 BBB
         7          9 AAA
        10         11 CCC
        12         13 BBB已选择5行。