MYSQL，对同⼀个表进⾏多次COUNT的SQL
交集
1. 通过⼦查询，把多个COUNT的值放到同⼀个表中
2. 在COUNT函数中，通过 id 去重，使得关联表时去除了多余的数据
SELECT t1.allocationDate,COUNT1 as "分配客户数",COUNT2 as "出⽅案客户数",ROUND(COUNT2 / COUNT1,2) as "⽅案转化率",COUNT3 as "成交客户数",ROUND(COUNT3 / COUNT2,2) as "交易转化率" FROM
(SELECT DATE_FORMAT(ar.allocationTime,"%Y-%m-%d") allocationDate,count(*) COUNT1 FROM appointment_record ar WHERE visible = 1
and allocationTime >= @allocationStartTime
and allocationTime < @allocationEndTime GROUP BY allocationDate) t1,
(SELECT DATE_FORMAT(ar.allocationTime,"%Y-%m-%d") allocationDate,count(DISTINCT ar.id) COUNT2 FROM appointment_record ar
JOIN insurance_plan ip on ar.userId = ip.userId
WHERE visible = 1
and allocationTime >= @allocationStartTime
and allocationTime < @allocationEndTime GROUP BY allocationDate
) t2 ,
( SELECT DATE_FORMAT(ar.allocationTime,"%Y-%m-%d") allocationDate,count(*) COUNT3 FROM appointment_record ar WHERE ar.validOrders > 0 and visible = 1
and allocationTime >= @allocationStartTime
and allocationTime < @allocationEndTime GROUP BY allocationDate ) t3
WHERE t2.allocationDate = t1.allocationDate and t2.allocationDate = t3.allocationDate;
不断叠加
⾮交集，可不断叠加
SELECT t1.paymentDate,sum1 as "净增标保", t2.count1 as "公司分配", t3.count2 as "⾃有客户",t4.count3 as "客户转介绍",t5.count4 as "其他" from
(SELECT DATE_FORMAT(io.paymentTime,"%Y-%m-%d") paymentDate,SUM(standardPremium) sum1 FROM insurance_order io WHERE io.visible = 1  GROUP BY paymentDate ) t1
LEFT JOIN
(SELECT DATE_FORMAT(io.paymentTime,"%Y-%m-%d") paymentDate,count(0) count1 FROM insurance_order io
LEFT JOIN user_info ui on io.userId = ui.id LEFT JOIN channel cl on cl.id = ui.channel
WHERE io.visible = 1 and cl.channelTypeId = 1 GROUP BY paymentDate) t2
on t1.paymentDate = t2.paymentDate
mysql group by order byLEFT JOIN
(SELECT DATE_FORMAT(io.paymentTime,"%Y-%m-%d") paymentDate,count(0) count2 FROM insurance_order io
LEFT JOIN user_info ui on io.userId = ui.id
LEFT JOIN channel cl on cl.id = ui.channel
WHERE io.visible = 1 and cl.channelTypeId = 2 GROUP BY paymentDate) t3
on t1.paymentDate = t3.paymentDate
LEFT JOIN
(SELECT DATE_FORMAT(io.paymentTime,"%Y-%m-%d") paymentDate,count(0) count3 FROM insurance_order io
LEFT JOIN user_info ui on io.userId = ui.id
LEFT JOIN channel cl on cl.id = ui.channel
WHERE io.visible = 1 and cl.channelTypeId = 3 GROUP BY paymentDate) t4
on t1.paymentDate = t4.paymentDate
LEFT JOIN
(SELECT DATE_FORMAT(io.paymentTime,"%Y-%m-%d") paymentDate,count(0) count4 FROM insurance_order io
LEFT JOIN user_info ui on io.userId = ui.id
LEFT JOIN channel cl on cl.id = ui.channel
WHERE io.visible = 1 and cl.channelTypeId = 99 GROUP BY paymentDate) t5
on t1.paymentDate = t5.paymentDate ;