日期:2014-05-16 浏览次数:20726 次
CREATE TABLE `20121105_teacher` ( `teacher_id` int(11) NOT NULL, `school_id` int(11) NOT NULL, PRIMARY KEY (`teacher_id`), KEY `20121105_teacher_idx_school` (`school_id`) ) ENGINE=InnoDB
CREATE TABLE `20121105_subject_teacher_class` ( `teacher_id` int(11) NOT NULL, `subj` varchar(10) NOT NULL, `class` varchar(10) NOT NULL, PRIMARY KEY (`teacher_id`,`subj`,`class`) ) ENGINE=InnoDB
select tid,GROUP_CONCAT( cls SEPARATOR '##') c1 from ( select teacher_id tid,CONCAT(subj,':',GROUP_CONCAT(class)) cls from 20121105_subject_teacher_class stc GROUP BY teacher_id,subj ) t GROUP BY tid,
select teacher_id, ( select GROUP_CONCAT( cls SEPARATOR ' ## ') from ( select teacher_id tid,CONCAT(subj,':',GROUP_CONCAT(class)) cls from 20121105_subject_teacher_class stc where stc.teacher_id=t1.teacher_id GROUP BY teacher_id,subj) t GROUP BY tid ) from 20121105_teacher t1 where school_id=2
select teacher_id, ( select GROUP_CONCAT( cls SEPARATOR ' ## ') from ( select teacher_id tid,CONCAT(subj,':',GROUP_CONCAT(class)) cls from 20121105_subject_teacher_class stc GROUP BY teacher_id,subj) t where t.tid=t1.teacher_id GROUP BY tid ) from 20121105_teacher t1 where school_id=2
CREATE FUNCTION `20121105f`(p_teacher_id int) RETURNS varchar(2000) READS SQL DATA BEGIN DECLARE v_result VARCHAR(2000); DECLARE EXIT HANDLER for not found return null; select GROUP_CONCAT( cls SEPARATOR ' ## ') into v_result from ( select teacher_id tid,CONCAT(subj,':',GROUP_CONCAT(class)) cls from 20121105_subject_teacher_class stc where stc.teacher_id=p_teacher_id GROUP BY teacher_id,subj ) t GROUP BY tid; return v_result; END
select SQL_NO_CACHE teacher_id, 20121105f(teacher_id) from 20121105_teacher t1 where school_id=2
select t1.teacher_id,t2.c1 from 20121105_teacher t1 left join ( select tid,GROUP_CONCAT( cls SEPARATOR '##') c1 from ( select teacher_id tid,CONCAT(subj,':',GROUP_CONCAT(class)) cls from 20121105_subject_teacher_class stc GROUP BY teacher_id,subj ) t GROUP BY tid ) t2 on t1.teacher_id=t2.tid where school_id=2
select t1.teacher_id,t2.c1 from 20121105_teacher t1 left join ( select tid,GROUP_CONCAT( cls SEPARATOR '##') c1 from ( select stc.teacher_id tid,CONCAT(subj,':',GROUP_CONCAT(class)) cls from 20121105_subject_teacher_class stc ,20121105_teacher te where stc.teacher_id=te.teacher_id and te.school_id=2 GROUP BY stc.teacher_id,subj ) t GROUP BY tid ) t2 on t1.teacher_id=t2.tid where school_id=2