用sql语句如何得到这样结果?440301:
1.test-18
2.test-4
3.test-8
4.test-31
.
.
.
440303:
1.test-7
2.test-33
.
.
.
依次类推,顺序是按record_id升序排列。附上测试数据。
create table TEST
(
RECORD_ID NUMBER not null,
SOURCE_CODE VARCHAR2(42),
QUES_TITLE VARCHAR2(2000)
);
insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (1, '440307', 'test-1');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (96, '440304', 'test-2');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (97, '440301', 'test-3');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (52, '440301', 'test-4');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (53, '440307', 'test-5');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (55, '440307', 'test-6');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (56, '440303', 'test-7');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (62, '440301', 'test-8');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (64, '440304', 'test-9');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (101, '440304', 'test-10');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (102, '440307', 'test-11');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (103, '440304', 'test-12');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (104, '440304', 'test-13');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (32, '440307', 'test-14');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (35, '440304', 'test-15');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (36, '440304', 'test-16');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (46, '440304', 'test-17');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (48, '440301', 'test-18');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (109, '440307', 'test-19');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (119, '440304', 'test-20');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (120, '440305', 'test-21');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (121, '440304', 'test-22');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (124, '440305', 'test-23');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (92, '440304', 'test-24');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (93, '440304', 'test-25');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (106, '440304', 'test-26');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (108, '440304', 'test-27');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (71, '440306', 'test-28');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (69, '440307', 'test-29');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (70, '440307', 'test-30');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (72, '440301', 'test-31');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (73, '440301', 'test-32');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (76, '440303', 'test-33');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (82, '440307', 'test-34');
commit;
1.test-18
2.test-4
3.test-8
4.test-31
.
.
.
440303:
1.test-7
2.test-33
.
.
.
依次类推,顺序是按record_id升序排列。附上测试数据。
create table TEST
(
RECORD_ID NUMBER not null,
SOURCE_CODE VARCHAR2(42),
QUES_TITLE VARCHAR2(2000)
);
insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (1, '440307', 'test-1');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (96, '440304', 'test-2');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (97, '440301', 'test-3');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (52, '440301', 'test-4');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (53, '440307', 'test-5');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (55, '440307', 'test-6');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (56, '440303', 'test-7');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (62, '440301', 'test-8');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (64, '440304', 'test-9');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (101, '440304', 'test-10');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (102, '440307', 'test-11');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (103, '440304', 'test-12');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (104, '440304', 'test-13');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (32, '440307', 'test-14');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (35, '440304', 'test-15');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (36, '440304', 'test-16');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (46, '440304', 'test-17');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (48, '440301', 'test-18');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (109, '440307', 'test-19');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (119, '440304', 'test-20');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (120, '440305', 'test-21');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (121, '440304', 'test-22');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (124, '440305', 'test-23');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (92, '440304', 'test-24');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (93, '440304', 'test-25');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (106, '440304', 'test-26');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (108, '440304', 'test-27');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (71, '440306', 'test-28');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (69, '440307', 'test-29');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (70, '440307', 'test-30');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (72, '440301', 'test-31');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (73, '440301', 'test-32');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (76, '440303', 'test-33');insert into test (RECORD_ID, SOURCE_CODE, QUES_TITLE)
values (82, '440307', 'test-34');
commit;
form TEST
group by SOURCE_CODE
order by RECORD_ID
440301 1.test-18
440301 2.test-4
440301 3.test-8
440301 4.test-31 440303 1.test-7
440303 2.test-33
440301 2.test-4
440301 3.test-8
440301 4.test-31 440303 1.test-7
440303 2.test-33
select SOURCE_CODE, row_number() over(partition by SOURCE_CODE order by RECORD_ID) rn, QUES_TITLE
from test
select SOURCE_CODE, row_number() over(partition by SOURCE_CODE order by RECORD_ID)||'.'|| QUES_TITLE
from test
from(
select grouping(c) g ,c,t
from(
select t.source_code || ':' c,
row_number() over(partition by t.source_code order by t.record_id) || t.ques_title t
from test t
)
group by cube(c,t)
)
where g=0
order by c,nvl(substr(t,0,1),-1)
--------------------------------------------------------------------------------
440301:
1test-18
2test-4
3test-8
4test-31
5test-32
6test-3
440303:
1test-7
2test-33
440304:
10test-13
14test-22
13test-20
12test-27
11test-26
1test-15
2test-16
3test-17
4test-9
NVL(T,C)
--------------------------------------------------------------------------------
5test-24
6test-25
7test-2
8test-10
9test-12
440305:
1test-21
2test-23
440306:
1test-28
440307:
1test-1
2test-14
3test-5
4test-6
5test-29
6test-30
7test-34
8test-11
9test-19
from(
select a.source_code,a.rn,a.ques_title,b.rid
from
(select source_code,rn,ques_title
from
(
select record_id,source_code,ques_title,
row_number() over(partition by source_code order by record_id) rn
from test
)) a,
(select null rn,source_code,rownum rid
from(
select max(rn) rn,source_code
from
(
select record_id,source_code,ques_title,
row_number() over(partition by source_code order by record_id) rn
from test)
group by source_code)) b
where a.source_code=b.source_code
union
(select null source_code,rn,source_code ques_title,rownum rid
from(
select max(rn) rn,source_code
from
(
select record_id,source_code,ques_title,
row_number() over(partition by source_code order by record_id) rn
from test)
group by source_code)))
order by rid,source_code nulls first,rn
/
from(
select grouping(c) g ,c,t
from(
select t.source_code || ':' c,
row_number() over(partition by t.source_code order by t.record_id) ||',' ||t.ques_title t
from test t
)
group by cube(c,t)
)
where g=0
order by c,nvl(to_number(substr(t,0,instr(t,',')-1)),-1)