sql条件统计

select
		count(DISTINCT pmsInfo.id,pmsInfo.categoryId = 127 or null) msaj,
		-- count(DISTINCT pmsInfo.id,pmsInfo.categoryId = 128 or null) xsaj,
		count(DISTINCT pmsInfo.id,pmsInfo.causeOfActionId IN (2,3,4,5,6,7,8,9,10,11,12,13,14) or null) whgj_cause,
		count(DISTINCT caseCustomer.id,caseCustomer.identityCategoryId = 46 and customerFeature.featureId = 99  or null) wcnr_id,
		-- count(DISTINCT caseCustomer.id,caseCustomer.identityCategoryId = 46 and customerFeature.featureId = 100  or null) cjr_id,

		-- count(DISTINCT caseCustomer.id,caseCustomer.identityCategoryId = 43 or null) gsqy_id,
		-- count(DISTINCT caseCustomer.id,caseCustomer.identityCategoryId = 44 or null) jgdw_id,
    (case when pmsInfo.categoryId = 135 THEN consultSum.amount ELSE caseSum.amount end) amount,
		DATE_FORMAT(pmsInfo.bulidApproveTime,'%Y%m%d') statisticTime,
		officeBase.officeName
    from
    pms_info pmsInfo
    LEFT JOIN office_base officeBase on pmsInfo.tenantId = officeBase.id
    LEFT JOIN fms_case_summary caseSum on pmsInfo.id = caseSum.infoId
    LEFT JOIN fms_consult_summary consultSum on pmsInfo.id = consultSum.infoId
		LEFT JOIN crm_case_customer caseCustomer ON pmsInfo.id = caseCustomer.infoId
    LEFT JOIN crm_case_customer_feature customerFeature ON caseCustomer.id = customerFeature.customerId

where pmsInfo.isDeleted = false and pmsInfo.tenantId = 5 and pmsInfo.typeId = 0
AND pmsInfo.bulidApproveTime >= '2019-01-01 00:00:01' and pmsInfo.bulidApproveTime <= '2019-03-14 23:59:59'
AND pmsInfo.statusId IN (1,2,3,4,5,6,7,12,13)
GROUP BY statisticTime

结果：

根据不同的类型取不同表的字段：

(case when pmsInfo.categoryId = 135 THEN consultSum.amount ELSE caseSum.amount end) amount,
(case when pmsInfo.categoryId = 135 THEN DATE_FORMAT(consultSum.createTime,'%Y%m%d') ELSE DATE_FORMAT(caseSum.createTime,'%Y%m%d') end) statisticTime

如果categoryId 等于135，取consultSum表的amount 和 consultSum表的createTime。
如果不等于，取caseSum表的amount 和 caseSum表的createTime。并取别名

日期格式化：

DATE_FORMAT(consultSum.createTime,'%Y%m%d') ：将日期格式化，例：（2019-01-10 00:00:00）—>20190110

根据条件统计：

count(pmsInfo.categoryId = 127 or null) msaj,    //pmsInfo.categoryId  == 127时统计，注意加or null 不加不管用

根据条件in统计：

count(pmsInfo.causeOfActionId IN (2,3,4,5,6,7,8,9,10,11,12,13,14) or null) whgj_cause,  //pmsInfo.causeOfActionId  等于后边括号内任意一个时统计，注意加or null 不加不管用

多条件and统计：

count(caseCustomer.identityCategoryId = 46 and customerFeature.featureId = 99  or null) wcnr_id,  //caseCustomer.identityCategoryId  = 46 且 customerFeature.featureId = 99 时统计，注意加or null 不加不管用

多条件统计并根据某字段去重：

count(DISTINCT caseCustomer.id,caseCustomer.identityCategoryId = 46 and customerFeature.featureId = 99  or null) wcnr_id,
//根据caseCustomer.id去重，并且当caseCustomer.identityCategoryId = 46 和customerFeature.featureId = 99时统计