有如下一张表,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
望高手指教,急急急急急急急急急
---------------------------------------------------------------
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 bbb
SQL> 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
---------------------------------------------------------------
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行。