各位好:
现有一问题:表中有三个字段col1,col2,col3,col1是序列字段,现在要根据col2和col3做分组,无解,求助!
例:t_table:
col1 col2 col3
1 A 1
2 A 1
3 B 1
4 B 1
5 C 2
6 C 2
7 A 1
8 A 2
9 B 2
10 B 2
需要将以上表记录分组成:
col1 col2 col3
2 A 1
4 B 1
6 C 2
7 A 1
8 A 2
10 B 2
求解???
...全文
14612打赏收藏
oracle分组问题
各位好: 现有一问题:表中有三个字段col1,col2,col3,col1是序列字段,现在要根据col2和col3做分组,无解,求助! 例:t_table: col1 col2 col3 1 A 1 2 A 1 3 B 1 4 B 1 5 C 2 6 C 2 7 A 1 8 A 2 9 B 2 10 B 2 需要将以上表记录分组成: col1 col2 col3 2 A 1 4 B 1 6 C 2 7 A 1 8 A 2 10 B 2 求解???
insert into zh (COL1, COL2, COL3)
values (4, 'B', '1');
insert into zh (COL1, COL2, COL3)
values (3, 'B', '1');
insert into zh (COL1, COL2, COL3)
values (2, 'A', '1');
insert into zh (COL1, COL2, COL3)
values (1, 'A', '1');
insert into zh (COL1, COL2, COL3)
values (6, 'C', '2');
insert into zh (COL1, COL2, COL3)
values (7, 'A', '1');
insert into zh (COL1, COL2, COL3)
values (8, 'A', '2');
insert into zh (COL1, COL2, COL3)
values (9, 'B', '2');
insert into zh (COL1, COL2, COL3)
values (10, 'B', '2');
select * from zh;
select col1,col2,col3 from (
select b.*,
case when (col2=(select col2 from zh a where a.col1 =b.col1+1) and col3=(select col3 from zh a where a.col1 =b.col1+1) ) then 1 end bz
from zh b order by col1) where bz is null;
insert into zh (COL1, COL2, COL3)
values (4, 'B', '1');
insert into zh (COL1, COL2, COL3)
values (3, 'B', '1');
insert into zh (COL1, COL2, COL3)
values (2, 'A', '1');
insert into zh (COL1, COL2, COL3)
values (1, 'A', '1');
insert into zh (COL1, COL2, COL3)
values (6, 'C', '2');
insert into zh (COL1, COL2, COL3)
values (7, 'A', '1');
insert into zh (COL1, COL2, COL3)
values (8, 'A', '2');
insert into zh (COL1, COL2, COL3)
values (9, 'B', '2');
insert into zh (COL1, COL2, COL3)
values (10, 'B', '2');
select * from zh;
select col1,col2,col3 from (
select b.*,
case when (col2=(select col2 from zh a where a.col1 =b.col1+1) and col3=(select col3 from zh a where a.col1 =b.col1+1) ) then 1 end bz
from zh b order by col1) where bz is null;