原来的sql语句：
select date(p.createTime) as typeDate,
"姓名碎片" as typeOneName,
coalesce((select count(*) from PaymentRecord as a where a.workerId="56142380e4b0d07cd49957b7" and date(a.createTime) =date(p.createTime) and a.imgType="0"),0) as typeOneAmount,
"地址碎片" as typeTwoName,
coalesce((select count(*) from PaymentRecord as a where a.workerId="56142380e4b0d07cd49957b7" and date(a.createTime) =date(p.createTime) and a.imgType="1"),0) as typeTwoAmount,
"质检" as typeThreeName,
coalesce((select count(*) from PaymentRecord as a where a.workerId="56142380e4b0d07cd49957b7" and date(a.createTime) =date(p.createTime) and a.note="text qc submit"),0) as typeThreeAmount
from PaymentRecord as p
where p.workerId="56142380e4b0d07cd49957b7" and date(p.createTime) in ("2015-10-27","2015-10-26","2015-10-20","2015-10-19","2015-10-18","2015-10-17","2015-10-16","2015-10-15","2015-10-14","2015-10-05") group by date(p.createTime) order by date(p.createTime) desc
查询结果（效果）
上面的sql语句是本人本人写的, 数据量小的时候没问题, 大于10w+的时候就奇慢无比. 麻烦哪位高手帮优化下啊.
查询目标是：在PaymentRecord表中 查询指定workerId 在 规定日期内 三种状态（姓名，地址，质检） 每天各自的数量。
解决方案
40
select date(p.createTime) as typeDate,
"姓名碎片" as typeOneName,
count(case p.imgType when "0" then 1 end) AS typeOneAmount,
"地址碎片" as typeTwoName,
count(case p.imgType when "1" then 1 end) AS typeTwoAmount,
"质检" as typeThreeName,
count(case p.note when "text qc submit" then 1 end) AS typeThreeAmount
from PaymentRecord as p
where p.workerId="56142380e4b0d07cd49957b7" and date(p.createTime) in ("2015-10-27","2015-10-26","2015-10-20","2015-10-19","2015-10-18","2015-10-17","2015-10-16","2015-10-15","2015-10-14","2015-10-05") group by date(p.createTime) order by date(p.createTime) desc
CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明求帮优化一个mysql 查询语句！
