请教高手，如何写写下面的sql语句？

显示全部楼层 · 2013-2-25 14:51:24

t1@ORCL> select * from c;
C1 C2
---------- ----------
1
1
2
1
4
1
5
0
6
0
8
1
9
1
10
1
11
1
12
1
13
0
14
1
15
1
16
1
17
1
18
1
19
1
c1列为编号，c2为状态(0,1),要求产生下面的效果：
1,4,1
5,6,0
8,12,1
13,13,0,
14,19,1
即按照c1的顺序，求出c2状态发生变化的开始及结束位置。
除了用plsql实现之外，能不能直接使用sql语句实现？
请高手指点。

千问 · 2013-2-25 14:51:24

建表语句，插入数据语句贴出来

千问 · 2013-2-25 14:51:24

目前我没有DB环境，你自己去测试一下！
代码具体如下：
selectmin(c1) start_c1,
max(c1) start_c2
c2
from
(select c1,
c2,
sum(rn) (order by c1) rn
from
(select c1,
c2,
decode(c2, lag(c2) over(order by c1), null, row_number() over(order by c1)) rn
from c))
group by rn, c2;

千问 · 2013-2-25 14:51:24

with tmp as(
select 1 as C1, 1 as C2 from dual union all
select 2,1 from dual union all
select 4,1 from dual union all
select 5,0 from dual union all
select 6,0 from dual union all
select 8,1 from dual union all
select 9,1 from dual union all
select 10,1 from dual union all
select 11,1 from dual union all
select 12,1 from dual union all
select 13,0 from dual union all
select 14,1 from dual union all
select 15,1 from dual union all
select 16,1 from dual union all
select 17,1 from dual union all
select 18,1 from dual union all
select 19,1 from dual)
--
select
min(a.c1) as b_c1,
max(a.c1) as e_c1,
a.c2
from
(select
i.c1,i.c2,
row_number() over(order by i.c1) as t1,
row_number() over(partition by i.c2 order by i.c1) as t2
from tmp i) a
group by a.c2,a.t1-a.t2
order by min(a.c1)

千问 · 2013-2-25 14:51:24

CREATE TABLE C(C1 INT ,C2 INT );
insert into C values(1,1);
insert into C values(2,1);
insert into C values(4,1);
insert into C values(5,0);
insert into C values(6,0);
insert into C values(8,1);
insert into C values(9,1);
insert into C values(10,1);
insert into C values(11,1);
insert into C values(12,1);
insert into C values(13,0);
insert into C values(14,1);
insert into C values(15,1);
insert into C values(16,1);
insert into C values(17,1);
insert into C values(18,1);
insert into C values(19,1);

千问 · 2013-2-25 14:51:24

稍显复杂了点：
SQL> with tmp as(
2select 1 c1, 1 c2 from dual union all
3select 2 c1, 1 from dual union all
4select 4 c1, 1 from dual union all
5select 5 c1, 0 from dual union all
6select 6 c1, 0 from dual union all
7select8, 1 from dual union all
8select9, 1 from dual union all
9select 10, 1 from dual union all
10select 11, 1 from dual union all
11select 12, 1 from dual union all
12select 13, 0 from dual union all
13select 14, 1 from dual union all
14select 15, 1 from dual union all
15select 16, 1 from dual union all
16select 17, 1 from dual union all
17select 18, 1 from dual union all
18select 19, 1 from dual)
--以下为语句：
19select wmsys.wm_concat(c1), c2
20from (select b.*,
21
row_number() over(partition by g order by c1) r1,
22
row_number() over(partition by g order by c1 desc) r2
23
from (select a.*, sum(t) over(order by c1) g
24
from (select t.*, decode(c2, lag(c2, 1, c2) over(order by c1), 0, 1) t
25
from tmp t) a) b) c
26 where r1 = 1
27or r2 = 1
28 group by g, c2
29/

WMSYS.WM_CONCAT(C1)
C2
-------------------------------------------------------------------------------- ----------
1,4
1
5,6
0
8,12
1
13
0
14,19
1

千问 · 2013-2-25 14:51:24

菜鸟来啦，学习学习！

千问 · 2013-2-25 14:51:24

非常感谢！
高手啊！
学习学习。
再次感谢！

千问 · 2013-2-25 14:51:24

误以为是字符串连接了：
SQL> with tmp as(
2select 1 c1, 1 c2 from dual union all
3select 2 c1, 1 from dual union all
4select 4 c1, 1 from dual union all
5select 5 c1, 0 from dual union all
6select 6 c1, 0 from dual union all
7select8, 1 from dual union all
8select9, 1 from dual union all
9select 10, 1 from dual union all
10select 11, 1 from dual union all
11select 12, 1 from dual union all
12select 13, 0 from dual union all
13select 14, 1 from dual union all
14select 15, 1 from dual union all
15select 16, 1 from dual union all
16select 17, 1 from dual union all
17select 18, 1 from dual union all
18select 19, 1 from dual)
19select min(c1), max(c1), c2
20from (select b.*,
21
row_number() over(partition by g order by c1) r1,
22
row_number() over(partition by g order by c1 desc) r2
23
from (select a.*, sum(t) over(order by c1) g
24
from (select t.*, decode(c2, lag(c2, 1, c2) over(order by c1), 0, 1) t
25
from tmp t) a) b) c
26 where r1 = 1
27or r2 = 1
28 group by g, c2
29 order by 1
30/

MIN(C1)MAX(C1) C2
---------- ---------- ----------
1
4
1
5
6
0
8 12
1
13 13
0
14 19
1

千问 · 2013-2-25 14:51:24

sum(rn) (order by c1) rn ?