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

bitsCN.com

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

Sql代码  

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个学校里 

Sql代码  

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能把每个教师的授课情况显示出来 

Sql代码  

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,  

那么把这个作为一个子查询呢?似乎很容易想到 

Sql代码  

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  

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

只能拿到外面一层 

Sql代码  

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  

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

所以可以建个函数 

Sql代码  

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  

然后这样用 

Sql代码  

select SQL_NO_CACHE teacher_id,  

20121105f(teacher_id)  

from 20121105_teacher t1 where school_id=2  

马上成瞬时的了. 

不用子查询,也可以用左连接的方法 

Sql代码  

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  

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

加上条件 

Sql代码  

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  

这样这个也成了瞬时的,不过筛选teacher的条件(school_id=2)执行了两次, 

如果这个条件比较耗资源,应该就更慢了