工作中开发的sql总结

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=1
  and a.tempfeeno = rpad(?, 40, ' ')
  AND a.otherno = e.contno
  and a.tempfeeno = b.tempfeeno
  AND a.tempfeetype in('1')
  AND a.paymoney <> 0
  AND a.tempfeeno = c.getnoticeno
  AND c.riskcode = d.riskcode
  AND (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=1
  and a.tempfeeno = rpad(?, 40, ' ')
  and a.otherno = b.contno
  AND a.tempfeetype in('1')
  AND a.paymoney <> 0
  -- AND a.OnlinePayFlag = '1' 
  AND a.tempfeeno = c.getnoticeno
  AND b.IsDonated = 'N'
  AND c.riskcode = d.riskcode
  AND 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