一个存储过程

首先是创建存储过程.

DROP PROCEDURE IF EXISTS `ExternalCallToComplete`;
DELIMITER $$
CREATE PROCEDURE ExternalCallToComplete()  
BEGIN

DROP TABLE IF EXISTS `temp1`;
CREATE TABLE temp1
AS
	SELECT s.name "问卷调查名",
			SUM(CASE WHEN sa.submit_type_id='1' THEN 1 ELSE 0 END)  "调查成功"   ,
			SUM(CASE WHEN sa.submit_type_id='2' THEN 1 ELSE 0 END) "同意开通" ,
			SUM(CASE WHEN sa.submit_type_id='3' THEN 1 ELSE 0 END) "空号" ,
			SUM(CASE WHEN sa.submit_type_id='4' THEN 1 ELSE 0 END) "总机" ,
			SUM(CASE WHEN sa.submit_type_id='5' THEN 1 ELSE 0 END) "传真" ,
			SUM(CASE WHEN sa.submit_type_id='6' THEN 1 ELSE 0 END) "停机" ,
			SUM(CASE WHEN sa.submit_type_id='7' THEN 1 ELSE 0 END) "占线" ,
			SUM(CASE WHEN sa.submit_type_id='8' THEN 1 ELSE 0 END) "用户关机" ,
			SUM(CASE WHEN sa.submit_type_id='9' THEN 1 ELSE 0 END) "无人接听" ,
			SUM(CASE WHEN sa.submit_type_id='10' THEN 1 ELSE 0 END) "外呼失败" ,
			SUM(CASE WHEN sa.submit_type_id='11' THEN 1 ELSE 0 END) "黑名单" ,
			SUM(CASE WHEN sa.submit_type_id='12' THEN 1 ELSE 0 END) "无法接通" ,
			SUM(CASE WHEN sa.submit_type_id='13' THEN 1 ELSE 0 END) "欠费停机" ,
			SUM(CASE WHEN sa.submit_type_id='14' THEN 1 ELSE 0 END) "主动挂机" ,
			SUM(CASE WHEN sa.submit_type_id='15' THEN 1 ELSE 0 END) "隔日联系" ,
			SUM(CASE WHEN sa.submit_type_id='16' THEN 1 ELSE 0 END) "接通后拒绝" ,
			SUM(CASE WHEN sa.submit_type_id='17' THEN 1 ELSE 0 END) "拒绝调查" ,
			SUM(CASE WHEN sa.submit_type_id='18' THEN 1 ELSE 0 END) "接通后考虑" ,
			SUM(CASE WHEN sa.submit_type_id='19' THEN 1 ELSE 0 END) "接通后主动挂机"  
	FROM submit_answer AS sa LEFT JOIN survey AS s ON s.id = sa.survey_id GROUP BY sa.survey_id ;
	
DROP TABLE IF EXISTS `temp2`;
CREATE TABLE temp2
as
SELECT 问卷调查名 "a", (调查成功+同意开通+空号+总机+传真+停机+占线+用户关机+无人接听+外呼失败+黑名单+无法接通+欠费停机+主动挂机+隔日联系+接通后拒绝+拒绝调查+接通后考虑+接通后主动挂机) 
"b",
调查成功 "c",
调查成功/(调查成功+同意开通+空号+总机+传真+停机+占线+用户关机+无人接听+外呼失败+黑名单+无法接通+欠费停机+主动挂机+隔日联系+接通后拒绝+拒绝调查+接通后考虑+接通后主动挂机) 
"d",
 主动挂机+隔日联系+接通后考虑 
"e",
主动挂机+隔日联系+接通后考虑 /(调查成功+同意开通+空号+总机+传真+停机+占线+用户关机+无人接听+外呼失败+黑名单+无法接通+欠费停机+主动挂机+隔日联系+接通后拒绝+拒绝调查+接通后考虑+接通后主动挂机) 
"f",
接通后拒绝+拒绝调查+接通后主动挂机 
"g",
(接通后拒绝+拒绝调查+接通后主动挂机) /(调查成功+同意开通+空号+总机+传真+停机+占线+用户关机+无人接听+外呼失败+黑名单+无法接通+欠费停机+主动挂机+隔日联系+接通后拒绝+拒绝调查+接通后考虑+接通后主动挂机) 
"h" 
FROM temp1 ;

end $$