Home > Database > Mysql Tutorial > mysql函数取代相关子查询(Correlated subquery)_MySQL

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

WBOY
Release: 2016-06-01 13:38:57
Original
1124 people have browsed it

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)执行了两次, 

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

 

bitsCN.com
Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template