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

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