select a.phone_number,a.call_date,a.status ,max(case when b.que_id='504' then form_value end) as `Q1` ,max(case when b.que_id='510' then form_value end) as `Q2` ,max(case when b.que_id='511' then form_value end) as `Q3` from visit_log a inner join ask_result_log b on b.uniqueid=a.uniqueid where a.call_date between '2012-11-16 00:00:00' and '2012-11-17 23:59:59' and b.call_date between '2012-11-16 00:00:00' and '2012-11-17 23:59:59' group by a.uniqueid
去掉 order by null 在 ask_result_log(uniqueid ,call_date)上建立索引。
不要全套子查询,用表的连接查询 select a.phone_number,a.call_date,a.status ,max(case when b.que_id='504' then form_value end) as `Q1` ,max(case when b.que_id='510' then form_value end) as `Q2` ,max(case when b.que_id='511' then form_value end) as `Q3` from visit_log a inner join ask_result_log b on b.uniqueid=a.uniqueid where a.call_date between '2012-11-16 00:00:00' and '2012-11-17 23:59:59' and b.call_date between '2012-11-16 00:00:00' and '2012-11-17 23:59:59' group by a.uniqueid;
,max(case when b.que_id='504' then form_value end) as `Q1`
,max(case when b.que_id='510' then form_value end) as `Q2`
,max(case when b.que_id='511' then form_value end) as `Q3`
from visit_log a inner join ask_result_log b on b.uniqueid=a.uniqueid
where
a.call_date between '2012-11-16 00:00:00' and '2012-11-17 23:59:59'
and b.call_date between '2012-11-16 00:00:00' and '2012-11-17 23:59:59'
group by a.uniqueid
在 ask_result_log(uniqueid ,call_date)上建立索引。
select a.phone_number,a.call_date,a.status
,max(case when b.que_id='504' then form_value end) as `Q1`
,max(case when b.que_id='510' then form_value end) as `Q2`
,max(case when b.que_id='511' then form_value end) as `Q3`
from visit_log a inner join ask_result_log b on b.uniqueid=a.uniqueid
where
a.call_date between '2012-11-16 00:00:00' and '2012-11-17 23:59:59'
and b.call_date between '2012-11-16 00:00:00' and '2012-11-17 23:59:59'
group by a.uniqueid;