mysql函数取代有关子查询(Correlated subquery)

mysql函数取代相关子查询(Correlated subquery)

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 

教师表,里面有1000个教师,随机分布在40个学校里

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

教师任课科目表,教师随机在24个班级内随机教三个科目.为了方便演示,直接将科目名称和班级名称放到数据库中
假设要查询教师的授课情况,每个教师这样显示
英语:11班,12班,8班##语文:13班,1班,21班,6班##数学:12班,14班,6班,7班

很容易想到这个sql能把每个教师的授课情况显示出来

select tid,GROUP_CONCAT( cls SEPARATOR '##') c1  from (select teacher_id tid,CONCAT(subj,':',GROUP_CONCAT(class)) clsfrom 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)) clsfrom 20121105_subject_teacher_class stc where stc.teacher_id=t1.teacher_idGROUP BY teacher_id,subj) t GROUP BY tid) from 20121105_teacher t1 where school_id=2

不过可惜在最里面那层子查询已经无法引用最外层的t1表的teacher_id这个字段了,

只能拿到外面一层

select  teacher_id,(select GROUP_CONCAT( cls SEPARATOR ' ## ') from (select teacher_id tid,CONCAT(subj,':',GROUP_CONCAT(class)) clsfrom 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

不过因为这样无法高效利用索引,这个sql花了0.05s

所以可以建个函数

CREATE  FUNCTION `20121105f`(p_teacher_id int) RETURNS varchar(2000)    READS SQL DATABEGINDECLARE 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)) clsfrom 20121105_subject_teacher_class stc where stc.teacher_id=p_teacher_idGROUP 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.c1from  20121105_teacher t1 left join ( select tid,GROUP_CONCAT( cls SEPARATOR '##') c1  from (select teacher_id tid,CONCAT(subj,':',GROUP_CONCAT(class)) clsfrom 20121105_subject_teacher_class stc GROUP BY teacher_id,subj) t  GROUP BY tid) t2on t1.teacher_id=t2.tidwhere school_id=2

这种情况下因为20121105_subject_teacher_class表没用索引,是0.04s左右
加上条件

select  t1.teacher_id,t2.c1from  20121105_teacher t1 left join ( select tid,GROUP_CONCAT( cls SEPARATOR '##') c1  from (select  stc.teacher_id tid,CONCAT(subj,':',GROUP_CONCAT(class)) clsfrom 20121105_subject_teacher_class stc ,20121105_teacher te  where stc.teacher_id=te.teacher_id and te.school_id=2GROUP BY  stc.teacher_id,subj) t  GROUP BY tid) t2on t1.teacher_id=t2.tidwhere school_id=2

这样这个也成了瞬时的,不过筛选teacher的条件(school_id=2)执行了两次,
如果这个条件比较耗资源,应该就更慢了

查看更多 下一篇