mysql非相关子查询_mysql函数取代相关子查询(Correlatedsubquery)

CREATE TABLE &＃96;20121105_teacher&＃96; (

&＃96;teacher_id&＃96; int(11) NOT NULL,

&＃96;school_id&＃96; int(11) NOT NULL,

PRIMARY KEY (&＃96;teacher_id&＃96;),

KEY &＃96;20121105_teacher_idx_school&＃96; (&＃96;school_id&＃96;)

) ENGINE&＃61;InnoDB

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

CREATE TABLE &＃96;20121105_subject_teacher_class&＃96; (

&＃96;teacher_id&＃96; int(11) NOT NULL,

&＃96;subj&＃96; varchar(10) NOT NULL,

&＃96;class&＃96; varchar(10) NOT NULL,

PRIMARY KEY (&＃96;teacher_id&＃96;,&＃96;subj&＃96;,&＃96;class&＃96;)

) ENGINE&＃61;InnoDB

教师任课科目表,教师随机在24个班级内随机教三个科目.为了方便演示,直接将科目名称和班级名称放到数据库中

假设要查询教师的授课情况,每个教师这样显示

英语:11班,12班,8班##语文:13班,1班,21班,6班##数学:12班,14班,6班,7班

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

select tid,GROUP_CONCAT( cls SEPARATOR &＃39;##&＃39;) c1 from

(

select teacher_id tid,CONCAT(subj,&＃39;:&＃39;,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 &＃39; ## &＃39;) from

(

select teacher_id tid,CONCAT(subj,&＃39;:&＃39;,GROUP_CONCAT(class)) cls

from 20121105_subject_teacher_class stc where stc.teacher_id&＃61;t1.teacher_id

GROUP BY teacher_id,subj) t GROUP BY tid

)

from 20121105_teacher t1 where school_id&＃61;2

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

只能拿到外面一层

select teacher_id,

(

select GROUP_CONCAT( cls SEPARATOR &＃39; ## &＃39;) from

(

select teacher_id tid,CONCAT(subj,&＃39;:&＃39;,GROUP_CONCAT(class)) cls

from 20121105_subject_teacher_class stc

GROUP BY teacher_id,subj) t where t.tid&＃61;t1.teacher_id GROUP BY tid

)

from 20121105_teacher t1 where school_id&＃61;2

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

所以可以建个函数

CREATE FUNCTION &＃96;20121105f&＃96;(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 &＃39; ## &＃39;) into v_result from

(

select teacher_id tid,CONCAT(subj,&＃39;:&＃39;,GROUP_CONCAT(class)) cls

from 20121105_subject_teacher_class stc where stc.teacher_id&＃61;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&＃61;2

马上成瞬时的了.

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

select t1.teacher_id,t2.c1

from 20121105_teacher t1

left join (

select tid,GROUP_CONCAT( cls SEPARATOR &＃39;##&＃39;) c1 from

(

select teacher_id tid,CONCAT(subj,&＃39;:&＃39;,GROUP_CONCAT(class)) cls

from 20121105_subject_teacher_class stc

GROUP BY teacher_id,subj

) t GROUP BY tid

) t2

on t1.teacher_id&＃61;t2.tid

where school_id&＃61;2

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

加上条件

select t1.teacher_id,t2.c1

from 20121105_teacher t1

left join (

select tid,GROUP_CONCAT( cls SEPARATOR &＃39;##&＃39;) c1 from

(

select stc.teacher_id tid,CONCAT(subj,&＃39;:&＃39;,GROUP_CONCAT(class)) cls

from 20121105_subject_teacher_class stc ,20121105_teacher te

where stc.teacher_id&＃61;te.teacher_id and te.school_id&＃61;2

GROUP BY stc.teacher_id,subj

) t GROUP BY tid

) t2

on t1.teacher_id&＃61;t2.tid

where school_id&＃61;2

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

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

分享到&＃xff1a;

2012-11-05 16:07

浏览 1724

分类:数据库

评论