oracle排序,取前十的实例

àì夳堔傛蜴生んèń 2021-11-26 14:32 468阅读 0赞

## 一:测试表 ##

\-- Create table

create table SRPT\_CKMXB

(

nbjgh VARCHAR2(30),

bz VARCHAR2(3),

rq VARCHAR2(20),

zh VARCHAR2(60),

khh VARCHAR2(20),

khmc VARCHAR2(200),

khdm VARCHAR2(100),

khlx VARCHAR2(4),

djzkhlx VARCHAR2(20),

qygm VARCHAR2(20),

ckje NUMBER(20,2),

ye NUMBER(20,2),

yezm NUMBER(20,2),

ll NUMBER(20,4),

jtlx NUMBER(20,2),

qxrq DATE,

dqrq DATE,

syqxt NUMBER(20),

syqxy NUMBER(20,2),

ysqxt NUMBER(20),

ysqxy NUMBER(20,2),

gsbs VARCHAR2(2),

ckcz VARCHAR2(10),

dhbs VARCHAR2(4),

dkzt VARCHAR2(20),

kglx VARCHAR2(20),

sffr VARCHAR2(20),

sfkj VARCHAR2(20),

sfsn VARCHAR2(20),

autoid VARCHAR2(32 CHAR)

)

tablespace ZKBBPT\_DATA

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 33M

next 1M

minextents 1

maxextents unlimited

);

## 二:测试数据 ##

insert into srpt\_ckmxb (NBJGH, BZ, RQ, ZH, KHH, KHMC, KHDM, KHLX, DJZKHLX, QYGM, CKJE, YE, YEZM, LL, JTLX, QXRQ, DQRQ, SYQXT, SYQXY, YSQXT, YSQXY, GSBS, CKCZ, DHBS, DKZT, KGLX, SFFR, SFKJ, SFSN, AUTOID)  
values ('ck0g4403000', 'BWB', '20190630', '201201010045301', '453', '宝湾供应链管理（无锡）有限公司', 'MA1T7HC9-X', '01', null, '04', 0.00, 0.00, 0.00, null, null, to\_date('30-06-2019', 'dd-mm-yyyy'), null, null, null, null, null, '01', '04', '02', '02', '01', '02', null, null, null);

insert into srpt\_ckmxb (NBJGH, BZ, RQ, ZH, KHH, KHMC, KHDM, KHLX, DJZKHLX, QYGM, CKJE, YE, YEZM, LL, JTLX, QXRQ, DQRQ, SYQXT, SYQXY, YSQXT, YSQXY, GSBS, CKCZ, DHBS, DKZT, KGLX, SFFR, SFKJ, SFSN, AUTOID)  
values ('ck0g4403000', 'BWB', '20190630', '201201010045501', '455', '漳州宝湾国际物流有限公司', 'MA2Y9MEN-8', '01', null, '04', 0.00, 0.00, 0.00, null, null, to\_date('30-06-2019', 'dd-mm-yyyy'), null, null, null, null, null, '01', '04', '02', '02', '01', '02', null, null, null);

insert into srpt\_ckmxb (NBJGH, BZ, RQ, ZH, KHH, KHMC, KHDM, KHLX, DJZKHLX, QYGM, CKJE, YE, YEZM, LL, JTLX, QXRQ, DQRQ, SYQXT, SYQXY, YSQXT, YSQXY, GSBS, CKCZ, DHBS, DKZT, KGLX, SFFR, SFKJ, SFSN, AUTOID)  
values ('ck0g4403000', 'BWB', '20190630', '201201010045601', '456', '漳州福美供应链管理有限公司', 'MA2YBW49-8', '01', null, '04', 0.00, 0.00, 0.00, null, null, to\_date('30-06-2019', 'dd-mm-yyyy'), null, null, null, null, null, '01', '04', '02', '02', '01', '02', null, null, null);

insert into srpt\_ckmxb (NBJGH, BZ, RQ, ZH, KHH, KHMC, KHDM, KHLX, DJZKHLX, QYGM, CKJE, YE, YEZM, LL, JTLX, QXRQ, DQRQ, SYQXT, SYQXY, YSQXT, YSQXY, GSBS, CKCZ, DHBS, DKZT, KGLX, SFFR, SFKJ, SFSN, AUTOID)  
values ('ck0g4403000', 'BWB', '20190630', '201201010045901', '459', '四川广汉宝湾国际物流有限公司', 'MA6383U1-1', '01', null, '04', 0.00, 0.00, 0.00, null, null, to\_date('30-06-2019', 'dd-mm-yyyy'), null, null, null, null, null, '01', '04', '02', '02', '01', '02', null, null, null);

insert into srpt\_ckmxb (NBJGH, BZ, RQ, ZH, KHH, KHMC, KHDM, KHLX, DJZKHLX, QYGM, CKJE, YE, YEZM, LL, JTLX, QXRQ, DQRQ, SYQXT, SYQXY, YSQXT, YSQXY, GSBS, CKCZ, DHBS, DKZT, KGLX, SFFR, SFKJ, SFSN, AUTOID)  
values ('ck0g4403000', 'BWB', '20190630', '201201010046001', '460', '贵阳双龙宝湾华业国际物流有限公司', 'MA6ECHY1-7', '01', null, '03', 0.00, 0.00, 0.00, null, null, to\_date('30-06-2019', 'dd-mm-yyyy'), null, null, null, null, null, '01', '04', '02', '02', '01', '02', null, null, null);

insert into srpt\_ckmxb (NBJGH, BZ, RQ, ZH, KHH, KHMC, KHDM, KHLX, DJZKHLX, QYGM, CKJE, YE, YEZM, LL, JTLX, QXRQ, DQRQ, SYQXT, SYQXY, YSQXT, YSQXY, GSBS, CKCZ, DHBS, DKZT, KGLX, SFFR, SFKJ, SFSN, AUTOID)  
values ('ck0g4403000', 'BWB', '20190630', '201201010046101', '461', '云南滇中宝湾物流有限公司', 'MA6K9DUN-X', '01', null, '04', 0.00, 0.00, 0.00, null, null, to\_date('30-06-2019', 'dd-mm-yyyy'), null, null, null, null, null, '01', '04', '02', '02', '01', '02', null, null, null);

insert into srpt\_ckmxb (NBJGH, BZ, RQ, ZH, KHH, KHMC, KHDM, KHLX, DJZKHLX, QYGM, CKJE, YE, YEZM, LL, JTLX, QXRQ, DQRQ, SYQXT, SYQXY, YSQXT, YSQXY, GSBS, CKCZ, DHBS, DKZT, KGLX, SFFR, SFKJ, SFSN, AUTOID)  
values ('ck0g4403000', 'BWB', '20190630', '201201010046201', '462', '淮安宝湾国际物流有限公司', 'MA1WXBCH-3', '01', null, '04', 0.00, 0.00, 0.00, null, null, to\_date('30-06-2019', 'dd-mm-yyyy'), null, null, null, null, null, '01', '04', '02', '02', '01', '02', null, null, null);

insert into srpt\_ckmxb (NBJGH, BZ, RQ, ZH, KHH, KHMC, KHDM, KHLX, DJZKHLX, QYGM, CKJE, YE, YEZM, LL, JTLX, QXRQ, DQRQ, SYQXT, SYQXY, YSQXT, YSQXY, GSBS, CKCZ, DHBS, DKZT, KGLX, SFFR, SFKJ, SFSN, AUTOID)  
values ('ck0g4403000', 'BWB', '20190630', '201201010046301', '463', '杭州宝湾智能科技有限公司', 'MA2CDUY4-3', '01', null, '04', 0.00, 0.00, 0.00, null, null, to\_date('30-06-2019', 'dd-mm-yyyy'), null, null, null, null, null, '01', '04', '02', '02', '01', '02', null, null, null);

insert into srpt\_ckmxb (NBJGH, BZ, RQ, ZH, KHH, KHMC, KHDM, KHLX, DJZKHLX, QYGM, CKJE, YE, YEZM, LL, JTLX, QXRQ, DQRQ, SYQXT, SYQXY, YSQXT, YSQXY, GSBS, CKCZ, DHBS, DKZT, KGLX, SFFR, SFKJ, SFSN, AUTOID)  
values ('ck0g4403000', 'BWB', '20190630', '201201010046501', '465', '深圳赤湾石油基地后勤服务有限公司', 'MA5F9RFG-9', '01', null, '04', 0.00, 0.00, 0.00, null, null, to\_date('30-06-2019', 'dd-mm-yyyy'), null, null, null, null, null, '01', '04', '02', '02', '01', '02', null, null, null);

## 三:语句编写 ##

insert into srpt\_zdsjck(

NBJGH, BZ, RQ, KHMC, KHDM,CKYE,DQCK,ZGXCKBZ,PX

)

select

'ck0g4403000' AS NBJGH,

'CNY' AS BZ,

'20190630' AS RQ,

T1.KHMC AS KHMC,  ---客户名称

'' AS KHH,  ---客户号

T1.CKYE AS CKYE,   ---存款余额

'' AS DQCK,---定期存款

'' ZGXCKBZ, ---占各项存款比值

PX   ---排序

FROM

(

select T1.RQ,T1.KHMC,CKYE,T1.KHDM,ROW\_NUMBER() OVER (ORDER BY (NVL(CKYE,0)) desc ) PX

from

(

select RQ,KHMC,BZ,KHDM,SUM(YE)/10000 CKYE from SRPT\_CKMXB WHERE bz='CNY' and ye>0 and substr(khmc,-1,1)='司'

and rq='20190630' and nbjgh='ck0g4403000' GROUP BY RQ,BZ,KHMC,KHDM ) T1--存款余额

LEFT JOIN (SELECT KHMC,KHDM,SUM(YE) DQCK FROM SRPT\_CKMXB where bz='CNY' and ye>0 and substr(khmc,-1,1)='司' and rq='20190630' and nbjgh='ck0g4403000' AND CKCZ='01'

GROUP BY KHMC,KHDM) T2--定期存款

ON T1.KHMC=T2.KHMC

) T1 where T1.PX<=10

关键语句

ROW\_NUMBER() OVER (ORDER BY (NVL(CKYE,0)) desc ) PX