--有表如下:
create table T1
(
CUSTOMER_TEL_ID VARCHAR2(40) not null,
CUSTOMER_ID VARCHAR2(40),
TELEPHONE VARCHAR2(40),
IS_EXTENTION NUMBER,
IS_FEE NUMBER,
OWNERSHIP NUMBER
);
--数据如下
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177699', 'SH02110024400', '63600258', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177701', 'SH02110024400', '63601190', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177705', 'SH02110024400', '63603386', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177706', 'SH02110024400', '63604625', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177707', 'SH02110024400', '63604689', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177700', 'SH02110024400', '63601049', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177713', 'SH02110024400', '63606662', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177717', 'SH02110024400', '63608886', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177718', 'SH02110024400', '63608887', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177722', 'SH02110024400', '63609618', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177725', 'SH02110024400', '63617944', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177726', 'SH02110024400', '63618945', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177727', 'SH02110024400', '8008204763', 1, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177691', 'SH02110024400', '63500153', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177692', 'SH02110024400', '63503509', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177693', 'SH02110024400', '63505903', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177694', 'SH02110024400', '63506587', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177695', 'SH02110024400', '63508382', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('756101', 'SH02110024400', '63508947', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('756098', 'SH02110024400', '63507310', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('865110', 'SH02110160646', '58794632', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('865108', 'SH02110160646', '58794630', 1, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('865111', 'SH02110160646', '58796040', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('865109', 'SH02110160646', '58794631', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('868008', 'SH02110161344', '58369551', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('868012', 'SH02110161344', '58828160', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('868013', 'SH02110161344', '58828161', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('868009', 'SH02110161344', '58369552', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('868011', 'SH02110161344', '58775700', 1, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('868014', 'SH02110161344', '58828170', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('868010', 'SH02110161344', '58369661', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('868015', 'SH02110161344', '58828171', 0, 0, 1);
commit;想得到如下结果,就是在执行select t.* from t1 t order by t.customer_id,t.is_fee,t.is_extention,t.ownership的基础之上,获取以字段分组t.customer_id的最靠前的3条记录(这个测试数据是3个,实际中就是N个)。
---------------------------------------------------------------------------------------
1 177699 SH02110024400 63600258 0 0 1
2 865110 SH02110160646 58794632 0 0 1
3 868008 SH02110161344 58369551 0 0 1这样的sql如何实现呢?
create table T1
(
CUSTOMER_TEL_ID VARCHAR2(40) not null,
CUSTOMER_ID VARCHAR2(40),
TELEPHONE VARCHAR2(40),
IS_EXTENTION NUMBER,
IS_FEE NUMBER,
OWNERSHIP NUMBER
);
--数据如下
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177699', 'SH02110024400', '63600258', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177701', 'SH02110024400', '63601190', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177705', 'SH02110024400', '63603386', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177706', 'SH02110024400', '63604625', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177707', 'SH02110024400', '63604689', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177700', 'SH02110024400', '63601049', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177713', 'SH02110024400', '63606662', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177717', 'SH02110024400', '63608886', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177718', 'SH02110024400', '63608887', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177722', 'SH02110024400', '63609618', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177725', 'SH02110024400', '63617944', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177726', 'SH02110024400', '63618945', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177727', 'SH02110024400', '8008204763', 1, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177691', 'SH02110024400', '63500153', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177692', 'SH02110024400', '63503509', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177693', 'SH02110024400', '63505903', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177694', 'SH02110024400', '63506587', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('177695', 'SH02110024400', '63508382', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('756101', 'SH02110024400', '63508947', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('756098', 'SH02110024400', '63507310', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('865110', 'SH02110160646', '58794632', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('865108', 'SH02110160646', '58794630', 1, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('865111', 'SH02110160646', '58796040', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('865109', 'SH02110160646', '58794631', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('868008', 'SH02110161344', '58369551', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('868012', 'SH02110161344', '58828160', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('868013', 'SH02110161344', '58828161', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('868009', 'SH02110161344', '58369552', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('868011', 'SH02110161344', '58775700', 1, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('868014', 'SH02110161344', '58828170', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('868010', 'SH02110161344', '58369661', 0, 0, 1);
insert into T1 (CUSTOMER_TEL_ID, CUSTOMER_ID, TELEPHONE, IS_EXTENTION, IS_FEE, OWNERSHIP)
values ('868015', 'SH02110161344', '58828171', 0, 0, 1);
commit;想得到如下结果,就是在执行select t.* from t1 t order by t.customer_id,t.is_fee,t.is_extention,t.ownership的基础之上,获取以字段分组t.customer_id的最靠前的3条记录(这个测试数据是3个,实际中就是N个)。
---------------------------------------------------------------------------------------
1 177699 SH02110024400 63600258 0 0 1
2 865110 SH02110160646 58794632 0 0 1
3 868008 SH02110161344 58369551 0 0 1这样的sql如何实现呢?
select * from
(select t.*,(row_number() over (partition by t.customer_id order by t.customer_id,t.is_fee,t.is_extention,t.ownership )) as num from t1 t
)
where num<=3
(select t.*,
row_number()over(partiton by t.customer_id order by t.is_fee,t.is_extention,t.ownership)rn,
rank()over(order by t.customer_id)rk from t1 t )
where rn=1 and rk<=3
select t.* from t1 t
where t.CUSTOMER_TEL_ID in(select min(CUSTOMER_TEL_ID) from t1 group by CUSTOMER_ID)
order by t.customer_id,t.is_fee,t.is_extention,t.ownership试试看
(select CUSTOMER_TEL_ID,CUSTOMER_ID,TELEPHONE,IS_EXTENTION,IS_FEE,OWNERSHIP,
row_number() over(partition by CUSTOMER_ID order by customer_id,is_fee,is_extention,ownership) rec
from t1) t
where t.rec = 1)
where rownum <= 3;
函数写错了
更正下
select * from
(select t.*,
row_number()over(partition by t.customer_id order by t.is_fee,t.is_extention,t.ownership)rn,
dense_rank()over(order by t.customer_id)rk from t1 t )
where rn=1 and rk <=3
CUSTOMER_TEL_ID CUSTOMER_ID TELEPHONE IS_EXTENTION IS_FEE OWNERSHIP RN RK
177699 SH02110024400 63600258 0 0 1 1 1
865110 SH02110160646 58794632 0 0 1 1 2
868008 SH02110161344 58369551 0 0 1 1 3
SQL> select customer_tel_id,customer_id,telephone,is_extention,is_fee,ownership
2 from(
3 select customer_tel_id,customer_id,telephone,is_extention,
4 is_fee,ownership,row_number() over(partition by customer_id
5 order by customer_tel_id) rn
6 from t1) where rn=1
7 /CUSTOMER_TEL_ID CUSTOMER_ID TELEPHONE IS_EXTENTION IS_FEE OWNERSHIP
---------------------------------------- ---------------------------------------- ---------------------------------------- ------------ ---------- ----------
177691 SH02110024400 63500153 0 0 1
865108 SH02110160646 58794630 1 0 1
868008 SH02110161344 58369551
order by customer_id,is_fee,is_extention,ownership
from(
select customer_tel_id,customer_id,telephone,is_extention,
is_fee,ownership,row_number() over(partition by customer_id
order by customer_id,is_fee,is_extention,ownership) rn
from t1) where rn=11 177699 SH02110024400 63600258 0 0 1
2 865110 SH02110160646 58794632 0 0 1
3 868008 SH02110161344 58369551 0 0 1
按从小到大排序取customer_id前3个,每个customer_id只取一行
这一行的is_fee,is_extention,owership在customer_id分组中的排序是排在第一行的