网站标题优化怎么做,天水嘉通建设集团网站,网站排版用什么软件,海口 做网站今天抽空整理下项目中的Sql语句* 项目中用到的Sql语句大全 Selcet 语句#xff0c;有简有难 Selcet 语句#xff0c;有简有难
基本的查询语句
select * from OLASCS.LLDC where CAPOLselect CATAX,CASU from OLASCS.LLDC where CAPOL IN (122345)SELECT EO,EANT, EAUS, sub…今天抽空整理下项目中的Sql语句* 项目中用到的Sql语句大全 Selcet 语句有简有难 Selcet 语句有简有难
基本的查询语句
select * from OLASCS.LLDC where CAPOLselect CATAX,CASU from OLASCS.LLDC where CAPOL IN (122345)SELECT EO,EANT, EAUS, substr(eafiller3,6,2), EATEO,LLA.* FROM OLASUF1.LLA where EAAGT in (109823764,000000518)SELECT * FROM OLAUF1.LLA WHERE SUBSTR(EAFILLER3,6,2) B2 AND EACO2586 AND EATUSA AND eaent in
(select ccagent1 from ola1.llcc where CCSITU1B2) and EAAGENCY in(,G16999997,,,,,,,,,,)比较复杂的查询语句嵌套好几个表的查询
select cbpo, cbpnfil||cban||cbbk||casis||cbplur,cbo,cstaus,cbsyy||cbmm||cbidd,cbcomt from oln2u1.lcb where cbpom in (select aipum from olaif1.laai where ailalD )
and cbrium 01
and cstus20 and cbsyy23 and cbsmm06
and cbnum in (select ccnum from olscuf1.llc where ccent1 in (select eaent from ola2f1.llea where easusA and easte ))
and cbum in (select apum from olaf1.laip) AND cbpnfil||cban||cbbk||brbis||cbdur like %% AND CO in (86)
select substr(COFILLER1,4,2) as type,substr(COFILLER2,12,8) as expire,substr(COFILLER2,10,1) as sheao,polnum,relat,co from olasu1.llo where polm in
(select YLPOM from olasuf1.CLATLDS where YLTE I and ylco1086 ) and co1086 and substr(COFILLER1,4,2)01
select CBO,cbplnum, cbplnfil||cbpan||cbbk||cbrsis||cbpldur,cbstus
from olas2uf1.llb where cbpoum in (
select VCLNUM from OLASF1.CCB where SUBSTR(VCBFILLER4,17,8) 20210727 and SUBSTR(VCBFILLER4,17,8) 20220727)
AND cbpanfil||cban||cbrk||cbasis||cbpndur like %P5B2% and cbstus20
and cbpolm in (Select AILNUM from olasf1.LAI where AICAL D and AITE 20040601 )select SUBSTR(DDS_PJ_CODE3,27,1) from OLAR1.LAJ where DDS_PJ_PLAN like %Y23P%select SUBSTR(DDS_PJ_CODE3,27,1) from OLASR1.LPJ where DDS_PJ_PLAN like %Y23LP%Select * from OLASR1.LPC where trim(PCPLAN) in (WB3N )
and PCAGE in(023,024,030,031) and PCCO in(0086) and PCBND in(00) And PCSEX in(F,U,M);SELECT * FROM OLAUF1.claCLRP WHERE PCRTYPE 6 AND PCRDUEANNV BETWEEN
20220530 AND 20220830 AND PCRACTION
AND SUBSTR(PCR_FILLER2,7,1)
AND SUBSTR(PCR_FILLER2,28,2) Y and PCR_POLNUM in (select cbponum from olas1.llb
where cbstatus20 ) SELECT * FROM OLA2UF1.clLRP WHERE PCRTYPE 6 AND PCRDUEANNV BETWEEN
20220530 AND 20220830 AND PCRACTION
AND SUBSTR(PCR_FILLER2,11,1)
AND SUBSTR(PCR_FILLER2,28,2) Y and PCR_POLNUM in (select cbpoum from olasf1.lldcb
where cbstatus20 )SELECT * FROM OLASCUF1.cladLRP WHERE PCRTYPE 6 AND PCRDUEANNV BETWEEN
20220530 AND 20220830 AND PCRACTION
AND SUBSTR(PCR_FILLER2,7,1)
AND SUBSTR(PCR_FILLER2,28,2) YYSELECT * FROM OL2UF1.claRP WHERE PCRTYPE 6 AND PCRDUEANNV BETWEEN
20220530 AND 20220830 AND PCRACTION
AND SUBSTR(PCR_FILLER2,11,1)
AND SUBSTR(PCR_FILLER2,28,2) YYSELECT pr.RPC_CO AS co,pr.RPC_POLNUM AS policyNum,min(pr.RPC_RIDNUM) AS riderNumFROMcloscs.CLAR prinner join cloudscs.CLADYLDS lds on (lds.YLCO pr.RPC_CO and lds.YLPOLNUM pr.RPC_POLNUM and lds.YLDUEANNV pr.RPC_DUEANN and lds.YLTYPE 6)inner join closcs.LCB cb on ( pr.RPC_CO cb.CBCO and pr.RPC_POLNUM cb.CBPOLNUM and pr.RPC_RIDNUM cb.CBRIDNUM and cb.CBSTATUS IN ( 20, 36 ) )WHERE pr.RPC_RIDACT ! XAND concat(pr.RPC_TYPE,pr.RPC_SUBTYPE) IN ( 6B, 5A )AND(pr.RPC_CO 2586 AND pr.RPC_POLNUM L001827020)
SELECT * FROM OLASCUF1.CLADRPR
WHERE (RPCCO, RPCPOLNUM ) IN ( SELECT RPCCO, RPCPOLNUM FROM OLASF1.CLA WHERE RPCTYPE||RPCSUBTYPE 5A AND RPCDUEANN 20230101
)
AND RPCDUEANN 20230101 select cbco,cbpolnum, cbplanfil||cbplan||cbrtbk||cbrbasis||cbplandur,cbstatus,cbissyy||cbissmm||cbissdd,cbcovamt
from ol1.llb where
cbpolnum like %H% and cbridnum01 and cbstatus20 and cbissyy22
and cbplanfil||cbplan||cbrtbk||cbrbasis||cbplandur not like %W%
and cbplanfil||cbplan||cbrtbk||cbrbasis||cbplandur like %MI%
and cbplanfil||cbplan||cbrtbk||cbrbasis||cbplandur not like %52%
and cbisscc 21
and cbpoum in (select pcr_polnum from olas1.clpclrp where pcr_type6and pcr_ackrlt )
and cbpolnum in (select RPC_polnum from olas1.clclrpR where RPC_type6)
order by cbissyy||cbissmm||cbissdd desc limit 100SELECT T1.CBCO,T1.CBPOLNUM,T1.CBMATXDTE, T2.VCBFILLER4 FROM
(SELECT DISTINCT CBCO,CBPOLNUM,CBRIDNUM,CBMATXCC||CBMATXYY||CBMATXMM||CBMATXDD CBMATXDTE FROM OLASCHPF1.LLDCB , OLASCR1.ADPA
WHERE CBPLANFIL||CBPLAN||CBRTBK||CBRBASIS||CBPLANDUR PLANCODE AND (CBCO PLANCO OR PLANCO 0086) AND substr(RESFLD3,10,1) 2
AND CBRIDNUM 01 ) T1
,
(SELECT DISTINCT CBCO,CBPOLNUM, SUBSTR(VCBFILLER4,17,8) VCBFILLER4 FROM OLASF1.CLCB, OLASCHPF1.LLDCB , OLA1.NLA
WHERE VCBCO CBCO AND VCBPOLNUM CBPOLNUM AND VCBRIDNUM CBRIDNUM
AND CBPLANFIL||CBPLAN||CBRTBK||CBRBASIS||CBPLANDUR PLANCODE AND (CBCO PLANCO OR PLANCO 0086) AND substr(RESFLD3,10,1) 6
AND CBRIDNUM 01) T2
WHERE T1.CBCO T2.CBCO AND T1.CBPNUM T2.CBLNUMSELECT * FROM OLAS1.LLB B WHERE
( B.CBCO ,B.CBPOLNUM ) IN ( SELECT CBCO, CBPOLNUM FROM OLAF1.LLB A
WHERE CBPLANFIL||CBPLAN||CBRTBK||CBRBASIS|| CBPLANDUR NOT IN (
SELECT YRTPLANCD FROM OLASCS2UF1.CLADPAYRT ) AND CBRIDNUM 01 AND CBSTTUS
20 AND CBMATXYY 28) AND CBPLANFIL||CBPLAN||CBRTBK||CBRBASIS|| CBPLANDUR LIKE %LADDR% AND B.CBSTATUS 20 SELECT * FROM LCB B WHERE( B.CBCO ,B.CBPOLNUM ) IN ( SELECT CBCO, CBPOLNUM FROM LLCB A WHERE CBPLANFIL||CBPLAN||CBRTBK||CBRBASIS|| CBPLANDUR NOT IN ( SELECT YRTPLANCD FROM CLADPAYRT ) AND CBRIDNUM 01 AND CBSTATUS 20 AND CBMATXYY 28) AND CBPLANFIL||CBPLAN||CBRTBK||CBRBASIS|| CBPLANDUR LIKE %AHI% AND B.CBMATXYY 26 AND B.CBSTUS 20 SELECT * FROM LLB B WHERE( B.CBCO ,B.CBPOLNUM ) IN ( SELECT CBCO, CBPOLNUM FROM LLB A WHERE CBPLANFIL||CBPLAN||CBRTBK||CBRBASIS|| CBPLANDUR NOT IN ( SELECT YRTPLANCD FROM CLADPAYRT ) AND CBRIDNUM 01 AND CBSTATUS 20 AND CBMATXYY 28) AND CBPLANFIL||CBPLAN||CBRTBK||CBRBASIS|| CBPLANDUR LIKE %ADDT% AND B.CBMATXYY 26 AND B.CBSTATUS 20 SELECT * FROM LLB B WHERE( B.CBCO ,B.CBPOLNUM ) IN ( SELECT CBCO, CBPOLNUM FROM LCB A WHERE CBPLANFIL||CBPLAN||CBRTBK||CBRBASIS|| CBPLANDUR NOT IN ( SELECT YRTPLANCD FROM CLADPAYRT ) AND CBRIDNUM 01 AND CBSTATUS 20 AND CBMATXYY 28) AND CBPLANFIL||CBPLAN||CBRTBK||CBRBASIS|| CBPLANDUR LIKE %DISADD% AND B.CBMATXYY 26 AND B.CBSTATUS 20 SELECT * FROM LLCB B WHERE( B.CBCO ,B.CBPOLNUM ) IN ( SELECT CBCO, CBPOLNUM FROM LCB A WHERE CBPLANFIL||CBPLAN||CBRTBK||CBRBASIS|| CBPLANDUR NOT IN ( SELECT YRTPLANCD FROM CLADPAYRT ) AND CBRIDNUM 01 AND CBSTATUS 20 AND CBMATXYY 28) AND CBPLANFIL||CBPLAN||CBRTBK||CBRBASIS|| CBPLANDUR LIKE %JSHR% AND B.CBMATXYY 26 AND B.CBSTATUS 20 SELECT * FROM LLB B WHERE( B.CBCO ,B.CBPOLNUM ) IN ( SELECT CBCO, CBPOLNUM FROM LLB A WHERE CBPLANFIL||CBPLAN||CBRTBK||CBRBASIS|| CBPLANDUR NOT IN ( SELECT YRTPLANCD FROM CLADPAYRT ) AND CBRIDNUM 01 AND CBSTATUS 20 AND CBMATXYY 28) AND CBPLANFIL||CBPLAN||CBRTBK||CBRBASIS|| CBPLANDUR LIKE %52LCI% AND B.CBMATXYY 26 AND B.CBSTATUS 20select SUBSTR(RPC_FILLER1,9,1) as RPC_FILLER1_9,substr(RPC_FILLER2,1,1) as RPC_FILLER2_1,RPC_PLNCODE, RPC_NEWPLAN,RPC_POLNUM,CLADPCLRPR. * from olacf1.CLADR where RPC_POLNUM in(
P592137517,);SELECT OPH_LETTYP, SUBSTR(OPHFILLER1,7,1)AS OPHFILLER1_7, SUBSTR(OPHFILLER2,18,1) AS OPHFILLER2_18,
SUBSTR(OPHFILLER2,19,1) AS OPHFILLER2_19,SUBSTR(OPHFILLER2,20,1) AS OPHFILLER2_20, SUBSTR(OPHFILLER2,24,4) AS OPHFILLER2_24_4,OPHACKRLT,OPH_ERRFLG,
CLADOPTLTH .* FROM OLA2UF2.CLADH WHERE OPH_POLNUM IN (
P592137517
);select substr(PCRFILLER2,26,1) as PCRFILLER2_26, substr(PCRFILLER2,27,1) as PCRFILLER2_27,substr(PCRFILLER2,2,10) as PCRFILLER2_2_10, PCR_ERRMSG,substr(PCR_ERRMSG,1,1) as PCR_ERRMSG_1,PCR_BILNT,CLADPCLRP. * from OLASCF1.CLACLRP where PCR_POLNUM in(
P592137517
);select distinct pco, ppolnum, ptype, pbakcod1, paccnum, pname, pchgdate,pstatus,pagtcod,NMIDNUM as INSIDNO,NMIDEXPDTE AS EXPDATE
from oln1uf1.cladprl LEFT JOIN OLN1F1.CLAM
on pcoNMCO AND ppolnumNMPOLNUM AND NMRIDNUM01 AND PRELAconcat(NMSEQGRP, substring(NMRELA,2,1)) AND NMRELA like B% AND NMNAMEPNAME
where ptypeO and pstatus A
-- and pco ? and pchgdate? and pchgdate ?
and pchguserNPOSSYS and ppolnum in (C201731233)
union
select distinct pco, ppolnum, ptype, pbakcod1, paccnum, pname, pchgdate,pstatus,pagtcod,NMIDNUM as INSIDNO,NMIDEXPDTE AS EXPDATE
from olauf1.cladrl LEFT JOIN OLASF1.CLAM
on pcoNMCO AND ppolnumNMPOLNUM AND NMRIDNUM01 AND PRELAconcat(NMSEQGRP, substring(NMRELA,2,1)) AND NMRELA like B% AND NMNAMEPNAME
where ptypeO and pstatus A and ppolnum in (C201731233)
-- and pco ? and pchgdate? and pchgdate ?
and pchguserNPOSSYS
and exists(select 1 from olauf1.cladyorh where pcophco and ppolnumphpolnum and pnamephname and ptypephtype and prelaphrela and phchguserNPOSSYS
-- and phchgdate? and phchgdate ?)
