Dear All:
大家好!小弟碰到一排序问题,请大侠赐教,不甚感激。
测试数据如下:create table TEST_SPE6
(
WELL_ID VARCHAR2(10),
TYPE_ID VARCHAR2(10),
DATES DATE
);insert into TEST_SPE6 (WELL_ID, TYPE_ID, DATES)
values ('A', '1', to_date('06-09-2010', 'dd-mm-yyyy'));
insert into TEST_SPE6 (WELL_ID, TYPE_ID, DATES)
values ('A', '1', to_date('22-09-2010', 'dd-mm-yyyy'));
insert into TEST_SPE6 (WELL_ID, TYPE_ID, DATES)
values ('A', '1', to_date('12-09-2010', 'dd-mm-yyyy'));
insert into TEST_SPE6 (WELL_ID, TYPE_ID, DATES)
values ('A', '1', to_date('24-09-2010', 'dd-mm-yyyy'));
insert into TEST_SPE6 (WELL_ID, TYPE_ID, DATES)
values ('A', '2', to_date('19-09-2010', 'dd-mm-yyyy'));
insert into TEST_SPE6 (WELL_ID, TYPE_ID, DATES)
values ('A', '2', to_date('13-09-2010', 'dd-mm-yyyy'));
insert into TEST_SPE6 (WELL_ID, TYPE_ID, DATES)
values ('A', '2', to_date('23-09-2010', 'dd-mm-yyyy'));
insert into TEST_SPE6 (WELL_ID, TYPE_ID, DATES)
values ('A', '2', to_date('06-09-2010', 'dd-mm-yyyy'));
insert into TEST_SPE6 (WELL_ID, TYPE_ID, DATES)
values ('A', '3', null);
insert into TEST_SPE6 (WELL_ID, TYPE_ID, DATES)
values ('A', '3', null);
insert into TEST_SPE6 (WELL_ID, TYPE_ID, DATES)
values ('A', '3', null);
insert into TEST_SPE6 (WELL_ID, TYPE_ID, DATES)
values ('A', '3', null);
commit;
按WELL_ID分组
1、对TYPE_ID字段进行组内排序
TYPE_ID = 1 的组内,按DATES 升序排序
TYPE_ID = 2 的组内,按DATES 降序排序
TYPE_ID 为null 的组内,按rownum排序2、整个well_id分组内,在按type 升序排列,type_id为null的在最后。最后希望的效果为:
WELL_ID TYPE_ID DATES order_no
A 1 2010-9-6 1
A 1 2010-9-12 2
A 1 2010-9-22 3
A 1 2010-9-24 4
A 2 2010-9-23 5
A 2 2010-9-19 6
A 2 2010-9-13 7
A 2 2010-9-6 8
A 3 9
A 3 10
A 3 11
A 3 12
大家好!小弟碰到一排序问题,请大侠赐教,不甚感激。
测试数据如下:create table TEST_SPE6
(
WELL_ID VARCHAR2(10),
TYPE_ID VARCHAR2(10),
DATES DATE
);insert into TEST_SPE6 (WELL_ID, TYPE_ID, DATES)
values ('A', '1', to_date('06-09-2010', 'dd-mm-yyyy'));
insert into TEST_SPE6 (WELL_ID, TYPE_ID, DATES)
values ('A', '1', to_date('22-09-2010', 'dd-mm-yyyy'));
insert into TEST_SPE6 (WELL_ID, TYPE_ID, DATES)
values ('A', '1', to_date('12-09-2010', 'dd-mm-yyyy'));
insert into TEST_SPE6 (WELL_ID, TYPE_ID, DATES)
values ('A', '1', to_date('24-09-2010', 'dd-mm-yyyy'));
insert into TEST_SPE6 (WELL_ID, TYPE_ID, DATES)
values ('A', '2', to_date('19-09-2010', 'dd-mm-yyyy'));
insert into TEST_SPE6 (WELL_ID, TYPE_ID, DATES)
values ('A', '2', to_date('13-09-2010', 'dd-mm-yyyy'));
insert into TEST_SPE6 (WELL_ID, TYPE_ID, DATES)
values ('A', '2', to_date('23-09-2010', 'dd-mm-yyyy'));
insert into TEST_SPE6 (WELL_ID, TYPE_ID, DATES)
values ('A', '2', to_date('06-09-2010', 'dd-mm-yyyy'));
insert into TEST_SPE6 (WELL_ID, TYPE_ID, DATES)
values ('A', '3', null);
insert into TEST_SPE6 (WELL_ID, TYPE_ID, DATES)
values ('A', '3', null);
insert into TEST_SPE6 (WELL_ID, TYPE_ID, DATES)
values ('A', '3', null);
insert into TEST_SPE6 (WELL_ID, TYPE_ID, DATES)
values ('A', '3', null);
commit;
按WELL_ID分组
1、对TYPE_ID字段进行组内排序
TYPE_ID = 1 的组内,按DATES 升序排序
TYPE_ID = 2 的组内,按DATES 降序排序
TYPE_ID 为null 的组内,按rownum排序2、整个well_id分组内,在按type 升序排列,type_id为null的在最后。最后希望的效果为:
WELL_ID TYPE_ID DATES order_no
A 1 2010-9-6 1
A 1 2010-9-12 2
A 1 2010-9-22 3
A 1 2010-9-24 4
A 2 2010-9-23 5
A 2 2010-9-19 6
A 2 2010-9-13 7
A 2 2010-9-6 8
A 3 9
A 3 10
A 3 11
A 3 12
from TEST_SPE6
order by well_id,type_id,decode(type_id,'1',dates-sysdate,'2',sysdate-dates);regards
附上哥改进的sql :
select *,row_number() over (partition by well_id order by type_id,
decode(type_id,'1',dates-sysdate,'2',sysdate-dates))
from TEST_SPE6;copy right reserved .Markzhu 2010-9-9在此再次感谢狂狼仔的帮助。