原来的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 查询语句！
