select LEFT(pp.add_time,4) date,
sum( case when pp.license_type='5' then 1 else 0 end) 'regist5',
sum( case when pp.license_type='7' then 1 else 0 end) 'regist7',
sum( case when pp.license_type='1002' then 1 else 0 end) 'regist1002',
sum( case when pp.license_type='1201' then 1 else 0 end) 'regist1201',
sum( case when pp.license_type='1202' then 1 else 0 end) 'regist1202'
from t_person_plan pp where pp.plan_status not in(6,7,9) and concat(LEFT(pp.dept_id,4),'00')='320200'
and left(pp.add_time,4)<'2017' GROUP BY LEFT(pp.add_time,4)
实现的结果如下图:
请教这个mysql怎么优化比较好呢?将近300万的数据,以后数据还会不断增多。谢谢。
sum( case when pp.license_type='5' then 1 else 0 end) 'regist5',
sum( case when pp.license_type='7' then 1 else 0 end) 'regist7',
sum( case when pp.license_type='1002' then 1 else 0 end) 'regist1002',
sum( case when pp.license_type='1201' then 1 else 0 end) 'regist1201',
sum( case when pp.license_type='1202' then 1 else 0 end) 'regist1202'
from t_person_plan pp where pp.plan_status not in(6,7,9) and concat(LEFT(pp.dept_id,4),'00')='320200'
and left(pp.add_time,4)<'2017' GROUP BY LEFT(pp.add_time,4)
实现的结果如下图:
请教这个mysql怎么优化比较好呢?将近300万的数据,以后数据还会不断增多。谢谢。
如果占比大的话,优化的余地不大,但计算列也可以提升一些性能
请教下,怎么叫计算列?你可以举个例子,写个简单的sql吗?谢谢