DELIMITER $$

USE `school`$$

DROP PROCEDURE IF EXISTS `system_teaPer_byteacher_split0417`$$

CREATE DEFINER=`schu`@`%` PROCEDURE `system_teaPer_byteacher_split0417`(
                                                        p_btime VARCHAR(100),
                                                        e_time VARCHAR(100),
                                                        p_gradeidStr VARCHAR(1000),
                                                        p_subjectidStr VARCHAR(1000),
                                                        p_clsIdStr VARCHAR(1000),
                                                        p_dcschool_id INT,
                                                        p_current_page INT,
                                                        p_page_size        INT,
                                                        p_sort_column VARCHAR(50),
                                                        OUT totalNum INT
                                                        )
BEGIN
        DECLARE tmp_sql VARCHAR(20000) DEFAULT '';
       
        SET tmp_sql = CONCAT("       
                SELECT cuser_id,teacher_name,courseCount,taskCount,endTaskCount
                ,IFNULL(compTaskPersonNum,0) compTaskPersonNum,IFNULL(personSumNum,0) personSumNum
                ,IFNULL(ROUND(IFNULL(compTaskPersonNum,0)/IFNULL(personSumNum,1)*100,2),'--') compBL
                ,IFNULL(coursePjSumScore,0) coursePjSumScore,coursePjStuCount
                ,IFNULL(ROUND(IFNULL(coursePjSumScore,0)/coursePjStuCount,2),0) coursePjScore
                FROM (
                -- 专题总数
                SELECT c.cuser_id,t.teacher_name,COUNT(DISTINCT c.course_id) courseCount,
                -- 专题下的任务总数（有效的）
                SUM((SELECT COUNT(DISTINCT task_id) FROM tp_task_info WHERE STATUS=1 AND course_id=c.course_id)) taskCount,
                -- 专题下已经结束的任务总数（有效的）
                SUM((
                        SELECT COUNT(DISTINCT ts.task_id) FROM tp_task_stat ts,tp_task_info t
                                WHERE ts.task_id=t.task_id
                                        AND t.course_id=c.course_id
                )) endTaskCount,
                -- 完成任务的总人数
                SUM((
                SELECT SUM(IFNULL(ts.done_person_num,0)) FROM tp_task_stat ts,tp_task_info t
                WHERE  t.task_id=ts.task_id AND t.STATUS=1 AND  t.course_id=c.course_id))
                 compTaskPersonNum,
                -- 应该参加完成任务的总人数
                 SUM((
                SELECT SUM(IFNULL(ts.person_num,0)) FROM tp_task_stat ts,tp_task_info t
                WHERE  t.task_id=ts.task_id AND t.STATUS=1 AND t.course_id=c.course_id))
                personSumNum
                -- 学生对专题的评价相加
                ,SUM((SELECT SUM(score) FROM score_info WHERE score_type=2 AND SCORE_OBJECT_ID=c.course_id)) coursePjSumScore
                -- 参与评价的学员总数
                ,SUM((SELECT COUNT(score_user_id) FROM score_info WHERE score_type=2 AND SCORE_OBJECT_ID=c.course_id)) coursePjStuCount
                 FROM tp_course_info c,user_info u,teacher_info t
                WHERE u.user_id=c.cuser_id AND u.ref=t.user_id
                        AND c.LOCAL_STATUS=1 -- 有效的
                        AND EXISTS(
                                SELECT 1 FROM tp_j_course_class cc,class_info cla WHERE
                                course_id=c.course_id and cla.class_id=cc.class_id
                                AND begin_time BETWEEN '",p_btime,"' AND '",e_time,"'");   
                               
                               
                               
                               
                                       
       
        IF p_gradeidStr IS NOT NULL THEN
                SET tmp_sql = CONCAT(tmp_sql," AND cc.grade_id IN(",p_gradeidStr,") ");               
        END IF;
        IF p_subjectidStr IS NOT NULL THEN
                SET tmp_sql = CONCAT(tmp_sql," AND cc.subject_id IN(",p_subjectidStr,") ");       
        END IF;
       
        IF p_clsIdStr IS NOT NULL THEN
                SET tmp_sql = CONCAT(tmp_sql," AND cc.CLASS_ID IN(",p_clsIdStr,") ");       
        END IF;                               
       
        SET tmp_sql = CONCAT(tmp_sql,")");
        IF p_dcschool_id IS NOT NULL THEN
                SET tmp_sql = CONCAT(tmp_sql," AND c.dc_school_id=",p_dcschool_id);       
        END IF;       
        SET tmp_sql = CONCAT(tmp_sql,"
                        GROUP BY t.teacher_name,c.cuser_id
                ) t");
       
       
        IF p_sort_column IS NOT NULL THEN
                SET tmp_sql=CONCAT(tmp_sql," order by ",p_sort_column);
          ELSE
                SET tmp_sql=CONCAT(tmp_sql," ORDER BY compBL DESC");          
        END IF;
        IF p_page_size IS NOT NULL AND p_current_page IS NOT NULL THEN
                SET tmp_sql=CONCAT(tmp_sql," limit ",(p_current_page-1)*p_page_size,",",p_page_size);
        END IF;
       
        SET @tmp_sql = tmp_sql;
        PREPARE stmt FROM @tmp_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
       
        SET tmp_sql = CONCAT("         
                -- 专题总数
                SELECT COUNT(distinct c.cuser_id) into @totalcount
                 FROM tp_course_info c
                WHERE c.LOCAL_STATUS=1 -- 有效的
                        AND EXISTS(
                                SELECT 1 FROM tp_j_course_class cc,class_info cla WHERE
                                course_id=c.course_id and cla.class_id=cc.class_id
                                AND begin_time BETWEEN '",p_btime,"' AND '",e_time,"'");
        IF p_gradeidStr IS NOT NULL THEN
                SET tmp_sql = CONCAT(tmp_sql," AND cc.grade_id IN(",p_gradeidStr,") ");               
        END IF;
        IF p_subjectidStr IS NOT NULL THEN
                SET tmp_sql = CONCAT(tmp_sql," AND cc.subject_id IN(",p_subjectidStr,") ");       
        END IF;
        IF p_dcschool_id IS NOT NULL THEN
                SET tmp_sql = CONCAT(tmp_sql," AND cla.dc_school_id=",p_dcschool_id);       
        END IF;       
        IF p_clsIdStr IS NOT NULL THEN
                SET tmp_sql = CONCAT(tmp_sql," AND cc.CLASS_ID IN(",p_clsIdStr,") ");       
        END IF;                       
       
        SET tmp_sql = CONCAT(tmp_sql,")");
        IF p_dcschool_id IS NOT NULL THEN
                SET tmp_sql = CONCAT(tmp_sql," AND c.dc_school_id=",p_dcschool_id);       
        END IF;       
               
        SET @tmp_sql2 = tmp_sql;
        PREPARE stmt2 FROM @tmp_sql2;
        EXECUTE stmt2;
        SET totalNum = @totalcount;
    END$$

DELIMITER ;

语句该优化的进行改造。存储过程在数据库中是必需的，是大数据、复杂关系数据处理的最好方式

生产上，不建议用存储过程！涉及相关业务的sql，得跟业务联系

生产上不用存储过程，那要存储过程干嘛滴……
这边业务全部在数据库端呢。

卷灰 发表于 2015-7-28 09:43
生产上不用存储过程，那要存储过程干嘛滴……
这边业务全部在数据库端呢。 ...

存储过程不建议用！让sql越简单越好！所以需要跟业务开发沟通，降低sql成本！提高DB性能

建议于业务沟通，优化一下需求，从中达到sql的可优化性