sql语句大全（db2、oracle、mysql、sql server）

left join 以左边的表为主，根据on关联条件，左边所有的数据都会显示出来，右边表中在左边没有匹配的数据会以Null来代替 -- right join 是以右边为主，根据on关联查询出数据，根据on关联出来的数据会选出来，左边表中在右边没有匹配的数据会以Null来代替 -- inner join 要根据on为查询条件 查询出左边和右边都能根据on匹配的数据 ，没有匹配的不会选出来，就用 inner join
##*****************一下都是DB2中的操作*********************************************************************

select * from conf_bt_before_task where name like ‘%信函预处理%‘ select * from conf_bt_after_task where name in(‘导出直催PDA外访总结码‘, ‘待外催案件自动退案处理‘,‘预测拨号结果更新‘,‘更新诉讼失效日期‘) SELECT T.* FROM TBL_PCCS_BIZ_ACT_TC T WHERE T.case_id = 120151021000061668 AND T.crt_time LIKE ‘2015-10-20%‘ AND EXISTS ( SELECT 1 FROM TBL_PCCS_BIZ_CUST_TEL AS E WHERE T.phone = E.phone AND data_source=‘HOST‘ ) WITH ur ----------------------------------------------------------------------------------------------------- SELECT case_id ,QUEUE_ID FROM TBL_PCCS_BIZ_CASE_CASEMAIN m WHERE value(m.QUEUE_ID,‘‘)<>‘E0100‘ AND NOT EXISTS ( SELECT 1 FROM TBL_PCCS_BIZ_CASE_ACCT a WHERE m.CASE_ID=a.CASE_ID AND value(a.STIS_FLAG,‘N‘)=‘N‘ ) ------------------------------------------------------------------------------------------------------ SELECT * FROM TBL_PCCS_BIZ_CASE_CASEMAIN m WHERE value(m.QUEUE_ID,‘‘)<>‘E0100‘ AND EXISTS ( SELECT 1 FROM TBL_PCCS_BIZ_CASE_ACCT a WHERE m.CASE_ID=a.CASE_ID AND value(a.STIS_FLAG,‘N‘)=‘N‘ ) ----------------------------------------------------------------------------------------------------- SELECT * FROM tbl_pccs_biz_case_casemain AS m LEFT JOIN ( SELECT DISTINCT case_id,acct_no FROM tbl_pccs_biz_case_acct WHERE stis_flag !=‘N‘ ) AS a ON m.case_id = a.case_id WHERE value(m.queue_id,‘‘)<>‘E0100‘ ----------------------------------------------------------------------------------------------------- SELECT * FROM tbl_pccs_biz_case_casemain WHERE case_id IN ( SELECT case_id FROM tbl_pccs_biz_case_acct WHERE stis_flag !=‘N‘) AND value(queue_id,‘‘)<>‘E0100‘ ----------------------------------------------------------------------------------------------------- SELECT CHK.ASST_CHK_ID, CHK.STATUS, CHK.CASE_ID, CHK.CUST_COMPANY,CHK.BEGIN_TIME, CHK.NODE_TIME_LMT,DIC.BUSIN_NAME FROM TBL_PCCS_BIZ_CASE_ASTCHK AS CHK LEFT OUTER JOIN TBL_PCCS_BNDICT_T_DICTIONARY AS DIC ON CHK.CUST_COMPANY = DIC.BUSIN_ID WHERE DATE(CHK.ASS_CHK_PROC_TIME_LMT) < ( DATE(‘2012-2-12‘)+ 1 DAY) AND CHK.STATUS IN(‘02‘, ‘05‘, ‘07‘, ‘09‘) AND DIC.BUSIN_TYPE_ID=‘PDA_Org_Center‘ WITH UR ----------------------------------------------------------------------------------------------------- SELECT CHAR(UPDATE_DATE,ISO),SOURCE,SEQ,CUSTID,FUN,ACCOUNT,STATUS_CODE,STATUS_CODE_2,MEMO,PAY_TYPE, OPERATOR,EXT, rownumber() over (ORDER BY SEQ) AS ROW_NEXT FROM BT_OPT_UPLOADHOST ----------------------------------------------------------------------------------------------------- --正常25号大于26号，数据库中 26是大于25号的 SELECT * FROM TBL_PCCS_BIZ_CASE_CASEMAIN WHERE IN_QUEUE_TIME <=(DATE(‘2020-08-30‘) - (2) DAY) AND QUEUE_ID =‘R0204‘ AND CASE_ID= 12016082800 AND AS_MAINTAINER <> ‘ASPECT‘ ----------------------------------------------------------------------------------------------------- SELECT T.* FROM TBL_PCCSWB_BIZ_OTHER_LINK_TEL AS T INNER JOIN WBCL_USR.TBL_PCCSWB_BIZ_CASE_CASEMAIN AS C ON T.CUST_NO=C.CUST_NO AND C.CUST_NO <>‘‘ INNER JOIN WBCL_USR.TBL_PCCSWB_BIZ_CASE_OUTAGREE AS O ON C.CASE_ID=O.CASE_ID AND EXISTS (SELECT 1 FROM DBCL_USR.TBL_PCCS_CONF_GL_GLOBAL as L where O.OA_CASE_PROTL_ADJ_DATE=L.BATCH_DATE FETCH FIRST ROWS ONLY) AND O.DEPUTE_DATE<SUBSTR(T.CRT_TIME,1,10) AND SUBSTR(T.CRT_TIME,1,10)<O.REAL_BACK_CASE_DATE AND O.CUST_NO<>‘‘ ----------------------------------------------------------------------------------------------------- SELECT caseId.CASE_ID FROM ( SELECT caseMain.CASE_ID FROM TBL_PCCS_BIZ_CASE_CASEMAIN AS caseMain WHERE EXISTS ( SELECT 1 FROM TBL_PCCS_BIZ_PREVIOUS_STOP_COLL AS proStopColl WHERE proStopColl.CUST_NO=caseMain.CUST_NO) AND caseMain.QUEUE_ID!=‘E0100‘) AS caseId -------------------------------------------------------------------------------------------------- SELECT * FROM TBL_PCCS_FP_DEDUCT_DETAIL_BATCH fetch first 1 rows only --清空表 ALTER TABLE TBL_PCCS_BIZ_TMP_HANDWORK_CREDITL ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE DELETE FROM TBL_PCCS_BIZ_TMP_HANDWORK_CREDITL DROP TABLE TBL_PCCS_FP_DEDUCT_DETAIL_BATCH CREATE TABLE TBL_PCCS_FP_DEDUCT_DETAIL_BATCH ( ID_PCCS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY, CURRENCY VARCHAR(4), NAME VARCHAR(20), AGE INTEGER, RELATION_LEVELS INTEGER DEFAULT 1, SALARY DECIMAL(15,2) DEFAULT 0.0, BIRTHDAY DATE, SYS_TIME TIMESTAMP, UPDATE_USER VARCHAR(20), DEBIT_CARD_NO VARCHAR(30) NOT NULL, REQ_DEDUCT_AMT DECIMAL(15,2),--double 类型 CERT_TYPE VARCHAR(4), CERT_NO VARCHAR(30), CRT_TIME TIMESTAMP DEFAULT CURRENT TIMESTAMP NOT NULL , LST_UPD_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP, LST_UPD_DATE DATE NOT NULL DEFAULT CURRENT DATE, LST_UPD_USER VARCHAR(32) NOT NULL DEFAULT ‘SYSTEM‘, REC_STATUS CHARACTER(1) DEFAULT ‘0‘ NOT NULL, SCR_LEVEL CHARACTER(2) NOT NULL DEFAULT ‘00‘, CONSTRAINT ident PRIMARY KEY(ID_PCCS) ) insert into TBL_PCCS_FP_DEDUCT_DETAIL_BATCH_HIS( BRANCH_ORG,COMM_TYPE,CURRENCY,ACCT_NO,DEBIT_CARD_NO, CREDIT_CARD_NO,REQ_DEDUCT_AMT,CERT_TYPE,CERT_NO,CREATE_TIME,CREATE_USER,LST_UPD_TIME, LST_UPD_USER) values(‘徐家汇分行‘,‘贷款‘,‘美元‘,‘908654678756‘,‘235643535435‘,‘686787797897‘,1000.00,‘身份‘, ‘3709231567802864471‘,‘2015-07-22 09:37:34‘,‘SYSTEM‘,current timestamp ,‘SYSTEM‘) SELECT DISTINCT bca.CUSTID, pc.CUST_NAME, pa.CARDID, pc.PRINCIPAL_RMB_AMT, pa.FEE_AMT, pda.BALANCE_AMT_CUR, pc.AUTO_STATE_CODE, pct.TEL_NO, pa.BRANCHID FROM BT_RPT_CREDIT_ACCT bca LEFT JOIN ( SELECT * FROM PCCS_CASE_ACCT WHERE PCCS_CASE_ACCT.MONEY_TYPE=‘156‘) pa ON pa.CASEID = bca.CASEID LEFT JOIN PCCS_CASE_CASEMAIN pc ON pc.CASEID = bca.CASEID LEFT JOIN PCCS_CASE_DEBIT_ACCT pda ON pda.CUSTID = bca.CUSTID LEFT JOIN( SELECT TEL_NO, custid FROM PCCS_CUST_TEL WHERE TEL_TYPE = ‘MB‘ AND CUST_REL = ‘SELF‘) AS pct ON pct.CUSTID = bca.custid ----------------------------------------------------------------------------------------------------- SELECT a.CustID, a.Tel_No, a.Dial_Time, b.DISPOSITION_NAME FROM BT_IMP_DAILERCONSQ a, CONF_AUTODIAL_Disposition b WHERE a.DispID=INT(b.DispositionId) ORDER BY a.CustID ----------------------------------------------------------------------------------------------------- select c.ACCT_NO from TBL_PCCS_BIZ_BT_TMP_CREDIT_ACCT c where not exists (select 1 from TBL_PCCS_BIZ_BT_TMP_DEBIT_ACCT d where d.CUST_NO = c.CUST_NO) ----------------------------------------------------------------------------------------------------- SELECT c.CUST_NAME, acct.CREDIT_CARD_NO, c.STATEMENT_DATE_STR, SUM( CASE WHEN acct.CURRENCY = ‘156‘ THEN value(CUR_CYC_STMT_BAL,0) END) AS CYCLE_BAL_AMT_RMB, SUM( CASE WHEN acct.CURRENCY = ‘840‘ THEN value(CUR_CYC_STMT_BAL,0) END) AS CYCLE_BAL_AMT_DOLLAR FROM TBL_PCCS_BIZ_CASE_CASEMAIN c LEFT JOIN TBL_PCCS_BIZ_CASE_ACCT acct ON c.CASE_ID = acct.CASE_ID WHERE c.CASE_ID = 120150818000000611 AND c.CARD_PROD=201 GROUP BY c.CUST_NAME, acct.CREDIT_CARD_NO, c.STATEMENT_DATE_STR ORDER BY c.CUST_NAME,acct.CREDIT_CARD_NO WITH ur ----------------------------------------------------------------------------------------------------- SELECT * FROM TBL_PCCS_BIZ_CASE_CASEMAIN a INNER JOIN ( SELECT COLL_ID, SCEN_ID , CENTER_NO FROM TBL_PCCS_CONF_GL_COLLIDDEAL WHERE DAIL_TYPE IN (‘PRDT‘, ‘PRVW‘) ) b ON a.CENTER_NO = b.CENTER_NO AND a.PROMPT_MSG_SEQ_NO = b.COLL_ID AND a.TRIAD_SCENID = b.SCEN_ID WHERE VALUE( ( SELECT T.check_date FROM TBL_PCCS_BIZ_ACT_ACTION T WHERE a.CASE_ID=T.CASE_ID AND T.check_date IS NOT NULL ORDER BY T.LST_UPD_TIME FETCH FIRST row only) ,CURRENT DATE) <= ( SELECT BATCH_DATE FROM TBL_PCCS_CONF_GL_GLOBAL FETCH FIRST rows only) AND( ( a.CASE_STOP_COLL_FLAG = ‘N‘ OR a.CASE_STOP_COLL_FLAG IS NULL) AND ( a.TEL_COLL_STOP_COL_FLG = ‘N‘ OR a.TEL_COLL_STOP_COL_FLG IS NULL) ) AND NOT EXISTS ( SELECT 1 FROM TBL_PCCS_BIZ_APPR_APPR T1 WHERE T1.CASE_ID=a.CASE_ID AND T1.APPR_STATUS=‘APPR‘ ) AND a.BELONG_BUSI_GRP = ‘MDFY‘ AND a.QUEUE_ID !=‘E0100‘ AND ( a.EVER_COLL_OPER_ID IS NULL OR EVER_COLL_OPER_ID=‘ASPECT‘) ----------------------------------------------------------------------------------------------------- select count( distinct(case_id) ) from wbcl_usr.TBL_PCCSWB_TMP_BIZ_CASE_CASEMAIN select CASE_ID,COUNT(1) from wbcl_usr.TBL_PCCSWB_TMP_BIZ_CASE_CASEMAIN GROUP BY CASE_ID ORDER BY 2 ----------------------------------------------------------------------------------------------------- SELECT M.CASE_ID, M.QUEUE_ID FROM TBL_PCCS_BIZ_CASE_CASEMAIN M, TBL_PCCS_BIZ_CASE_ACCT T, VIEW_CASE_TRANS V WHERE M.CASE_ID=T.CASE_ID AND T.ACCT_NO= V.ACCT_NO AND ( M.QUEUE_ID = ‘O0100‘ OR M.QUEUE_ID = ‘O0200‘) AND VALUE(M.COLL_NO,‘‘)=‘‘ AND M.FAKE_TYPE IS NULL AND EXISTS ( SELECT 1 FROM TBL_PCCS_CONF_GL_QUEUEDEF WHERE QUEUE_ID = M.LAST_QUEUE_NO AND TEAM_ID IN(‘TELE‘, ‘MDFY‘)) GROUP BY M.CASE_ID, M.QUEUE_ID, M.MIN_PAY_BAL_OF_FS_OA_BU HAVING SUM(V.TRANS_AMT) >= M.MIN_PAY_BAL_OF_FS_OA_BU / 2.0 ----------------------------------------------------------------------------------------------------- select max(G.appr_ser_no) from dbcl_usr.TBL_PCCS_BIZ_APPR_OAORGCHG G where LST_UPD_DATE = date(‘2015-12-31‘) and NEW_COLL_ORG is not null and NEW_COLL_ORG <>‘‘ group by G.case_id ----------------------------------------------------------------------------------------------------- select * from TBL_PCCS_BIZ_CUST_TEL where tel_no in ( select max(tel_no) from ( select * from TBL_PCCS_BIZ_CUST_TEL T where T.cust_no = ‘0019712660186‘ and T.phone in(select phone from TBL_PCCS_BIZ_CUST_TEL where DATA_SOURCE in(‘HOST‘) ) ) group by phone ) ----------------------------------------------------------------------------------------------------- -- CASE WHEN THEN 用法 SELECT MOVE_CODE,MOVE_TYPE,IS_VISIBLE, CASE MOVE_CODE WHEN ‘CUP‘ THEN ‘W‘ WHEN ‘LJYD‘ THEN ‘L‘ WHEN ‘BRYD‘ THEN ‘B‘ WHEN ‘WNXT‘ THEN ‘W‘ ELSE ‘E‘ END from TBL_PCCS_CONF_GL_ACTCODEDEF SELECT CASE WHEN DATA_SOURCE= ‘WLFK‘ THEN ‘网络发卡‘ WHEN DATA_SOURCE=‘SJWL‘ THEN ‘社交网络‘ WHEN DATA_SOURCE=‘BANK‘ THEN ‘人行‘ ELSE ‘其它‘ END from TBL_PCCS_BIZ_CUST_TEL_EXPAND ----------------------------------------------------------------------------------------------------- --N天不通 SELECT LST_UPD_DATE, SUM(EFFECTFLAG) AS STATUS FROM ( SELECT DISTINCT LST_UPD_DATE, ( CASE WHEN TEL_CODE IN (‘MESS‘,‘LESD‘, ‘LESP‘, ‘LESS‘,‘LESK‘, ‘LESR‘, ‘LESF‘,‘LESC‘‘LESX‘,‘PTP‘, ‘PTPD‘,‘PTPP‘, ‘PTPS‘, ‘PTPK‘,‘PTPR‘, ‘PTPF‘, ‘PTPC‘,‘PTPX‘,‘ALPA‘,‘QUIT‘,‘OOOC‘, ‘FEE‘,‘REGO‘,‘MOVE‘,‘MEET‘, ‘REST‘,‘WORK‘,‘ONTK‘,‘INSY‘,‘NOIN‘,‘KNOW‘,‘CUT‘,‘DLYD‘,‘DLYP‘,‘DLYS‘, ‘DLYF‘,‘DLYC‘,‘DLYX‘,‘BRKD‘, ‘BRKP‘,‘BRKS‘, ‘BRKK‘,‘BRKR‘,‘BRKF‘,‘BRKC‘, ‘BRKX‘, ‘CHEK‘,‘IIVR‘,‘REP‘) THEN 1 ELSE 0 END) AS EFFECTFLAG FROM TBL_PCCS_BIZ_ACT_TC WHERE CASE_ID = 120160823000081758 ) GROUP BY LST_UPD_DATE ORDER BY LST_UPD_DATE DESC WITH ur ----------------------------------------------------------------------------------------------------- SELECT LST_UPD_DATE, SUM(EFFECTFLAG) AS STATUS FROM ( SELECT DISTINCT LST_UPD_DATE,--对两个字段去重复 ( CASE WHEN MOVE_CODE IN (‘XZDK‘,‘QXDK‘,‘XZZB‘, ‘QXZB‘) THEN 1 ELSE 0 END) AS EFFECTFLAG FROM TBL_PCCS_BIZ_ACT_ACTION WHERE CASE_ID = 120160823000081756) GROUP BY LST_UPD_DATE ORDER BY LST_UPD_DATE DESC WITH ur ----------------------------------------------------------------------------------------------------- values date(‘2015-12-03‘); values substr(char(‘2015-12-03‘),9,2);-- 数据库下标是重1开始的 截取2位 values substr(char(‘2015-02-30‘),1,8); ----------------------------------------------------------------------------------------------------- SELECT B.* FROM ( SELECT A.*, ( CASE WHEN STATEMENT_DATE_STR > ( SELECT SUBSTR(CHAR(BATCH_DATE),9,2) FROM TBL_PCCS_CONF_GL_GLOBAL ) THEN ( SELECT SUBSTR(CHAR(BATCH_DATE + 1 MONTH),1,8) || A.STATEMENT_DATE_STR FROM TBL_PCCS_CONF_GL_GLOBAL ) ELSE ( SELECT SUBSTR(CHAR(BATCH_DATE),1,8) || A.STATEMENT_DATE_STR FROM TBL_PCCS_CONF_GL_GLOBAL ) END) AS CYCLE_DAY FROM TBL_PCCS_BIZ_CASE_CASEMAIN A WHERE value(A.EVER_COLL_OPER_ID,‘‘) <> ‘‘ AND CENTER_NO = ‘027‘ AND QUEUE_ID = ‘T0101‘ AND CASE_AMT >= 0.0 AND CASE_ID =120160823000081758) B WHERE ( SELECT BATCH_DATE + 1 days FROM TBL_PCCS_CONF_GL_GLOBAL) < B.CYCLE_DAY WITH ur ----------------------------------------------------------------------------------------------------- SELECT A.CITY AS AREA_CODE, A.ACT_ORG_ID, TO_CHAR(A.DEPUTE_DATE,‘yyyymm‘) AS OA_DATE, ( CASE WHEN A.CURRENCY = ‘840‘ THEN A.RECOVERY_AMT*4141 ELSE A.RECOVERY_AMT END) AS ACHIEVE_AMT FROM TBL_PCCS_BT_OPT_OAPMT A ----------------------------------------------------------------------------------------------------- DELETE FROM WBCL_USR.TBL_PCCSWB_TMP_BIZ_CUST_TEL CS WHERE CS.TEL_NO not IN ( SELECT MAX(G.TEL_NO) FROM WBCL_USR.TBL_PCCSWB_TMP_BIZ_CUST_TEL G GROUP BY G.CUST_NO,G.PHONE) ----------------------------------------------------------------------------------------------------- SELECT * FROM ( SELECT CUST_NO, CERT_NO, count num FROM TBL_PCCS_BIZ_CUST_CUSTOMER GROUP BY CUST_NO, CERT_NO ) as T where T.num>2 ----------------------------------------------------------------------------------------------------- -- 67897987 截取，从倒数第二位开始截取，截取两位 为87 一个参数就是从开始位置截取到最后 --db2 下标是从1开始的 select substr(cust_no,length(cust_no)-1,2) f