本人目前在进行某业务市平台往省平台的抽取和转换业务,其中市平台查询sql语句一张主表,四张从表,其中有需求统计主从表缺失的业务量。sql分享如下:
SELECT
SUM(IF(IF(AID>0,1,0) = 1, 1, 0)) AS 'AA业务总缺失量',
SUM(IF(IFNULL(AJZLSH,1) = 1, 1, 0)) AS 'BB导致业务缺失量',-- 如果AJZLSH为空,IFNULL函数执行结果为1,此时为sum(if(1=1,1,0)),结果自增1
SUM(IF(IFNULL(CID,1) = 1, 1, 0)) AS 'CC导致业务缺失数量',
SUM(IF(IFNULL(DID,1) = 1, 1, 0)) AS 'DD信息导致业务缺失量' ,
SUM(IF(IFNULL(BID,1) = 1, 1, 0)) AS 'EE导致业务缺失量'
FROM (
SELECT -- 先查询主表,左外链接从表,查询数据
A.ID AS AID,A.JZLSH AS AJZLSH,C.ID AS CID,D.ID AS DID,B.ID AS BID
FROM
OPT_FEE A
LEFT JOIN PT_INFORMATION C
ON C.JGDM = A.JGDM
AND C.KH = A.KH
AND C.KLX = A.KLX
LEFT JOIN OPT_ORGANIZATION_INFO D
ON D.JGDM = A.JGDM
LEFT JOIN OPT_RECORD B
ON A.JGDM = B.JGDM