SELECT m.phoneNumber,m.course,m.source,m.mobileArea,m.age,m.sex,m.regTime,d.mac, GROUP_CONCAT(d.lessonName) AS lname
FROM downloadrecord d LEFT JOIN(SELECT * FROM mobileuser ORDER BY id DESC)AS m ON d.mac=m.mac  
AND d.mac='8C-7B-9D-F0-6A-3D' GROUP BY d.mac


 
1:LEFT JOIN ON
左连接，将左边表所有记录全部列出，如果右边表对应的记录有值则将记录值列举出来
2：LEFT JOIN ON子查询
LEFT JOIN(SELECT * FROM mobileuser ORDER BY id DESC)AS m ON
如果右边表符合条件的记录有两个，则取ID最大的那个

3：GROUP_CONCAT(d.lessonName)......GROUP BY d.mac根据d.mac进行组合查询，同事对lessonName字段进行连接显示为一个字段lname

 
 

  不多说 直接上语句   
 
 
 

  SELECT
 
 
 

      a.id,
 
 
 

      a.thumbNail,
 
 
 

      a. NAME,
 
 
 

      a.marketPrice,
 
 
 

      a.memberPrice,
 
 
 

      a.personName,
 
 
 

      a. STATUS,
 
 
 

      a.recieveOrderDate,
 
 
 

      a.trackNumber,
 
 
 

      a.contact,
 
 
 

      a.reportSendDate,
 
 
 

      b.trackNumber,
 
 
 

      a.reportDownloadPath
 
 
 

  FROM
 
 
 

      (
 
 
 

          SELECT
 
 
 

              od.id,
 
 
 

              ps.thumbNail,
 
 
 

              ps. NAME,
 
 
 

              od.marketPrice,
 
 
 

              od.memberPrice,
 
 
 

              od.personName,
 
 
 

              od. STATUS,
 
 
 

              od.recieveOrderDate,
 
 
 

              ol.trackNumber,
 
 
 

              ol.contact,
 
 
 

              od.reportSendDate,
 
 
 

              od.reportSendOrderLogisticId,
 
 
 

              od.reportDownloadPath
 
 
 

          FROM
 
 
 

              orders.order_detail od
 
 
 

          LEFT JOIN orders.order_logistics ol ON od.recieveOrderLogisticId = ol.id
 
 
 

          LEFT JOIN orders.product_snapshot ps ON od.productSnapShotId = ps.id
 
 
 

          WHERE
 
 
 

              od.valid = TRUE
 
 
 

          AND ol.valid = TRUE
 
 
 

          AND od.orderId =?
 
 
 

      ) a
 
 
 

  LEFT JOIN (
 
 
 

      SELECT
 
 
 

          ol.trackNumber,
 
 
 

          od.id
 
 
 

      FROM
 
 
 

          orders.order_detail od
 
 
 

      LEFT JOIN orders.order_logistics ol ON od.reportSendOrderLogisticId = ol.id
 
 
 

      WHERE
 
 
 

          od.valid = TRUE
 
 
 

      AND ol.valid = TRUE
 
 
 

      AND od.orderId =?
 
 
 

  ) b ON a.id = b.id
 
 
 

   
 
 
 

   
 
 
 

  ps：解释下 对于Mysql中的语句，牵扯到左查询时  可以用left join关联  但一般对于不知道什么时候起别名 什么时候用括号的老铁们 真实头疼  所以发个模板 剩下的根据自己的业务具体实施。
 
 

背景:
 
 今天在做一个项目的时候需要关联查询，但是好奇想知道left join 和子查询哪一个比较合适。
 
于是做了个实验。
 
 子查询：
    SELECT a.*,(select content from yxhy_news_content  where
 yxhy_news_content.id=a.id) as content FROM `yxhy_news` `a` WHERE  `a`.`id` = '47'
 LIMIT 1    
 
查询时间为0.735
 
 
 
LEFT JOIN查询
 select * from yxhy_news a LEFT JOIN yxhy_news_content b on b.id=a.id where a.id=47;
 查询时间为0.188S
 
 
 
很明显，LEFT JOIN 要比子查询效率高。其原因是子表的ID是没有添加索引的。
 
在yxhy_news_content这个子表添加主键索引后实验结果如下。
 
LEFT JOIN查询结果：
 
select * from yxhy_news a LEFT JOIN yxhy_news_content b on b.id=a.id where a.id=61
 
时间为:0.055s
 
 
 
子查询结果：
 
SELECT a.*,(select content from yxhy_news_content  where 
 
yxhy_news_content.id=a.id) as content FROM `yxhy_news` `a` WHERE  `a`.`id` = 61 
 
LIMIT 1
 
时间: 0.046s
 
 
 
很明显子查询效果要比LEFT JOIN效率要高，但是还是不建议在FROM之前使用子查询。即使是有主键的情况下也不要这么使用。
 
 
 
100万数据表未建索引的情况下，连接会比用子查询快几倍，建索引后两种查询性能差不多。

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;





[img]http://dl2.iteye.com/upload/attachment/0109/7590/e36b2bb0-1e7f-3e30-b961-e50e5b6d3cb1.jpg[/img]






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 ;





[img]http://dl2.iteye.com/upload/attachment/0109/7592/107dfcfe-6ca8-3491-a0c1-07837bbd8470.png[/img]





每个表都有 50-100万的数据。

看来，效率差不多。