当前位置：首页 > news >正文

工作中开发的sql总结

news 2025/6/12 22:59:52

sql示例1：

SELECT a.AccountDate as 业务日期,a.tempfeeno as 业务号码,a.otherno    as 合同号码,c.riskcode as 保险产品,(select (case when mainpolno <> polno then mainpolno else polno end) from  ifrs17.ty_lcpol  where contno = a.otherno and riskcode = c.riskcode limit 1)  as 保单险种号码,a.managecom as 机构代码,a.managecom as 异地机构,a.salechnl  as 渠道代码,a.agentcom  as 代理机构,a.agentgroup as 代理人组,a.agentcode as 代理人编码,sum(c.sumactupaymoney) as 金额,(CASE WHEN IFNULL(sum(c.sumactupaymoney) , 0 ) > 0 THEN '正'ELSE '负' END) AS 金额符号,'CNY' as 币种,'1' as 个团标记,case when (SELECT count(1) FROM ifrs17.ty_LDCODE ldc WHERE ldc.CODETYPE='BZXB' and ldc.code=d.riskcode)=0 then '首期' when (SELECT count(1) FROM ifrs17.ty_LDCODE ldc WHERE ldc.CODETYPE='BZXB' and ldc.code=d.riskcode)>0 and (select lcc.renewcount from ifrs17.ty_lccont lcc where lcc.contno=a.otherno)>0 AND (select lcc.renewcount from ifrs17.ty_lccont lcc where lcc.contno=a.otherno)<(SELECT othersign FROM ifrs17.ty_LDCODE ldc WHERE ldc.CODETYPE='BZXB' and ldc.code=c.riskcode) then '续期' else '首期' end as 首续期标志,IFNULL(TRUNCATE((SELECT TIMESTAMPDIFF( MONTH, e.signdate, e.cvalidate )/12+1),0),0) AS 保单年度,b.paymode as 交费方式,IFNULL((SELECT costcenter FROM ifrs17.ty_laagent WHERE agentcode = e.agentcode LIMIT 1 ),'0')  as 成本中心,IFNULL((SELECT AES_VALUE FROM aes.aes_bus_aes_mapping WHERE CODE_ID = 'mxdnbwl' AND BUS_VALUE = a.managecom LIMIT 1),'0') as 明细段,(select code1 from  ifrs17.ty_ldcode1 where codetype='FundSeg' AND code=(select  risktype3  from  ifrs17.ty_lmriskapp where riskcode =c.riskcode limit 1 ) ) as 资金段,(CASE  WHEN (SELECT AES_VALUE2  FROM aes.aes_bus_aes_mapping  WHERE CODE_ID = 'txqdandzzglqd'  AND BUS_VALUE = e.salechnl   AND e.salechnl IN ('33', '55', '11', '12')  AND BUS_VALUE2 = (SELECT AngencyType  FROM ifrs17.ty_lacom    WHERE agentcom =  e.agentcom  LIMIT 1)) IS NOT NULL THEN(SELECT AES_VALUE2  FROM aes.aes_bus_aes_mapping  WHERE CODE_ID = 'txqdandzzglqd'  AND BUS_VALUE = e.salechnl   AND e.salechnl IN ('33', '55', '11', '12')   AND BUS_VALUE2 =(SELECT AngencyType   FROM ifrs17.ty_lacom    WHERE agentcom = e.agentcom LIMIT 1))WHEN (SELECT AES_VALUE2  FROM aes.aes_bus_aes_mapping  WHERE CODE_ID = 'txqdandzzglqd'  AND BUS_VALUE = e.salechnl   AND e.salechnl IN ('66', '99', '22', '10', '14', '13') LIMIT 1) IS NOT NULL THEN(SELECT AES_VALUE2  FROM aes.aes_bus_aes_mapping  WHERE CODE_ID = 'txqdandzzglqd'  AND BUS_VALUE = e.salechnl   AND e.salechnl IN ('66', '99', '22', '10', '14', '13') LIMIT 1)WHEN (SELECT AES_VALUE2  FROM aes.aes_bus_aes_mapping  WHERE CODE_ID = 'txqdandzzglqd'  AND BUS_VALUE = e.salechnl   AND e.salechnl IN ('09', '77', '88')   AND BUS_VALUE2 = (SELECT agentkind    FROM ifrs17.ty_laagent  WHERE agentcode =e.agentcode  LIMIT 1)) IS NOT NULL THEN(SELECT AES_VALUE2  FROM aes.aes_bus_aes_mapping  WHERE CODE_ID = 'txqdandzzglqd'  AND BUS_VALUE = e.salechnl   AND e.salechnl IN ('09', '77', '88')   AND BUS_VALUE2 =(SELECT agentkind  FROM ifrs17.ty_laagent  WHERE agentcode =e.agentcode LIMIT 1))    ELSE  '0'   END) AS 管理渠道,'预收保费' as 业务项目类型,'新单收费' as 财务项目类型,'业务费用'  as   收付费项目,'S000123' as 系统来源编码,'核心业务系统' as 系统来源名称,'C000123' as 业务分类编码,'Core_K_I17_0001231' as 业务场景细分编码,'个险新契约预收保费及保费收入确认' as 业务分类名称,'个险新契约预收保费' as 业务场景细分名称
FROM  ifrs17.ty_ljtempfee a,ifrs17.ty_ljtempfeeclass b,ifrs17.ty_ljapayperson c, ifrs17.ty_lmriskapp d,ifrs17.ty_lccont e
where 1=1and a.tempfeeno = rpad(?, 40, ' ')AND a.otherno = e.contnoand a.tempfeeno = b.tempfeenoAND a.tempfeetype in('1')AND a.paymoney <> 0AND a.tempfeeno = c.getnoticenoAND c.riskcode = d.riskcodeAND (a.paymentFlag is null or a.paymentFlag = '1')AND a.OnlinePayFlag = '0'-- AND a.paymentFlag = '0'-- AND e.conttype = '1'-- AND e.giveflag <> '1'
group by a.AccountDate , a.tempfeeno,a.otherno,c.riskcode,a.managecom,a.salechnl,a.agentcom,a.agentgroup,a.agentcode

sql示例2：

SELECT b.confirmdate as  业务日期,a.tempfeeno as 业务号码,c.grpcontno as 集体合同号码,a.otherno as 合同号码,c.polno as 保单险种号码,a.managecom as 机构代码,a.managecom as 异地机构,(CASE WHEN c.grpcontno='00000000000000000000' THEN (SELECT salechnl FROM  ifrs17.ty_lccont WHERE contno=c.contno) ELSE   (SELECT salechnl FROM  ifrs17.ty_lcgrpcont WHERE grpcontno = c.grpcontno limit 1) END) as 渠道代码,(CASE WHEN c.grpcontno='00000000000000000000' THEN(SELECT agentcom FROM  ifrs17.ty_lccont WHERE contno=c.contno limit 1) ELSE  (SELECT agentcom FROM  ifrs17.ty_lcagentcominfo WHERE policyno=c.grpcontno limit 1) END)  as 代理机构,(CASE WHEN c.grpcontno='00000000000000000000' THEN(SELECT agentgroup FROM  ifrs17.ty_lccont WHERE contno=c.contno limit 1) ELSE  (SELECT agentgroup FROM  ifrs17.ty_lcagenttocont WHERE policyno=c.grpcontno limit 1) END) as  代理人组,(CASE WHEN c.grpcontno='00000000000000000000'  THEN(SELECT agentcode FROM  ifrs17.ty_lccont WHERE contno=c.contno limit 1) ELSE   (SELECT agentcode FROM  ifrs17.ty_lcagenttocont WHERE policyno=c.grpcontno limit 1) END) as 代理人编码,sum(c.sumactupaymoney) as 金额,(CASE  WHEN IFNULL(sum(c.sumactupaymoney), 0 ) > 0 THEN '正' ELSE '负' END) AS 金额符号,'CNY' as 币种,(CASE WHEN c.grpcontno='00000000000000000000' THEN '1' ELSE '2' END) as  个团标记,a.paymode as 交费方式,IFNULL(TRUNCATE((SELECT TIMESTAMPDIFF( MONTH,(select signdate from ifrs17.ty_lcgrpcont where grpcontno = c.grpcontno   limit 1), (select cvalidate from ifrs17.ty_lcgrpcont where grpcontno = c.grpcontno   limit 1))/12+1),0),0) AS 保单年度,IFNULL((SELECT costcenter FROM ifrs17.ty_laagent WHERE agentcode =(CASE WHEN c.grpcontno='00000000000000000000'  THEN(SELECT agentcode FROM  ifrs17.ty_lccont WHERE contno=c.contno limit 1) ELSE   (SELECT agentcode FROM  ifrs17.ty_lcagenttocont WHERE policyno=c.grpcontno limit 1) END)  LIMIT 1 ),'0')  as 成本中心,IFNULL((SELECT AES_VALUE    FROM aes.aes_bus_aes_mapping  WHERE CODE_ID = 'mxdnbwl'    AND BUS_VALUE = a.policycom  LIMIT 1),  '0') as 明细段,(select code1 from ifrs17.ty_ldcode1 where codetype='FundSeg' AND code=d.risktype3 ) as 资金段 ,(case when a.managecom = a.policycom then '非集中收费'  else '集中收费' end) as  集中收付费标志,'新契约收费' as 业务项目类型,'预收保费' as 财务项目类型,'内部往来' as 收付费项目,'S000312' as 系统来源编码,'核心业务系统' as 系统来源名称,'C0026123' as 业务分类编码,'短险共保分期收款业务' as 业务分类名称,'Core_K_I17_002312' as 业务场景细分编码,'短期险新契约收费' as 业务场景细分名称
FROM  ifrs17.ty_ljtempfee a,ifrs17.ty_wxcontno b,ifrs17.ty_ljapayperson c, ifrs17.ty_lmriskapp d
WHERE  1=1and a.tempfeeno = rpad(?, 40, ' ')and a.otherno = b.contnoAND a.tempfeetype in('1')AND a.paymoney <> 0-- AND a.OnlinePayFlag = '1' AND a.tempfeeno = c.getnoticenoAND b.IsDonated = 'N'AND c.riskcode = d.riskcodeAND a.managecom <> a.policycom-- AND (a.PaymentFlag = '1' or a.PaymentFlag is null)-- AND d.RISKPERIOD in('M','S') -- 长短险标志
group by b.confirmdate, a.tempfeeno,c.grpcontno,a.otherno, c.polno, a.managecom,a.paymode,d.risktype3