left join 跟子查询

left join 和子查询

select * from (  select
			  (select name   from wp_teacher wt inner join wp_teacher_class wts on wt.teacherid = wts.teacherid where wts.classid = kcs.classid and wts.headmaster = 1 limit 1) AS teachername,
        (select wt.teacherid   from wp_teacher wt inner join wp_teacher_class wts on wt.teacherid = wts.teacherid where wts.classid = kcs.classid and wts.headmaster = 1 limit 1) AS teacherid,
			  (select name   from wp_student ws where ws.studentid= kcs.studentid limit 1) AS studentname,
			  (select name   from wp_keeper  wk where wk.keeperid = kcs.keeperid limit 1) AS user,
			  (select mobile from wp_keeper  wk where wk.keeperid = kcs.keeperid limit 1) AS usermobile, 
        (select title  from wp_classes wcs where wcs.classid = kcs.classid limit 1) AS classname, 
				wc.id AS id,
				wc.cardid AS cardid,
				wc.schoolid AS schoolid,
				wc.type AS type,
				wc.status AS status,
				wc.fuid AS fuid,
				wc.returnid AS returnid,
				kcs.ismain AS ismain,
				kcs.keeperid AS keeperid,
				kcs.studentid AS studentid,
				kcs.relation AS relation,
				kcs.classid AS classid,
				kcs.issendsms AS issendsms,
				kcs.returnid AS kcsreturnid from 
				( select  * from  wp_card order by cardid asc   limit 10,10 ) as wc left join wp_keeper_card_student kcs on wc.cardid = kcs.cardid order by cardid ) as ok;

select * from (
select 

wc.id AS id,
wc.cardid AS cardid,
wc.schoolid AS schoolid,
wc.type AS type,
wc.status AS status,
wc.fuid AS fuid,
wc.returnid AS returnid,

wk.name AS user,
wk.mobile AS usermobile,
ws.name AS studentname,
wca.title  AS classname,

wt.name AS teachername,
wt.teacherid AS teacherid,

kcs.ismain AS ismain,
kcs.keeperid AS keeperid,
kcs.studentid AS studentid,
kcs.relation AS relation,
kcs.classid AS classid,
kcs.issendsms AS issendsms,
kcs.returnid AS kcsreturnid 
from 
( select  * from  wp_card order by cardid asc limit 10,10  ) as wc
left join wp_keeper_card_student kcs on(wc.cardid = kcs.cardid)
left join wp_keeper wk on wk.keeperid = kcs.keeperid 
left join wp_classes wca on wca.classid = kcs.classid 
left join wp_student ws on ws.studentid = kcs.studentid
left join wp_teacher_class wts on (wts.classid = kcs.classid and wts.headmaster = 1 )
left join wp_teacher wt on wt.teacherid = wts.teacherid
) ok ;

每个表都有 50-100万的数据。
看来，效率差不多。