mysql中的left join用法 （及多条件查询

语法：FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2 
说明：table1, table2参数用于指定要将记录组合的表的名称。
field1, field2参数指定被联接的字段的名称。且这些字段必须有相同的数据类型及包含相同类型的数据，但它们不需要有相同的
名称。
compopr参数指定关系比较运算符："="， "<"， ">"， "<="， ">=" 或 "<>"。

例子：

select m1.Matename,MapName,dict.Fieldname,DesinNum,e1.Realname as name1,e2.Realname as name2,ConstrProjName,ChgNum,projdesignmaterial.Savetime,Remarks,projdesignmaterial.ID,projdesignmaterial.ProjID  from projdesignmaterial 

left join material m1 on projdesignmaterial.MateID = m1.ID

left join dict on projdesignmaterial.Unit = dict.ID

left join employee e1 on projdesignmaterial.CountID = e1.ID  
left join employee e2 on projdesignmaterial.ConformID = e2.ID

left join constrproj on projdesignmaterial.ProjID = constrproj.ConstrProjID WHERE projdesignmaterial.ID = '$ID'

高级应用  A和B  ,A和C 连接， 在中间也可进行B和C的左连接

SELECT u.mobile,t1.teamname,u.realname,u.address,u.regdate from team_users
                left join users u on team_users.userID=u.userid  
               left join team t1 on team_users.teamid=t1.teamid
               left join `match` m1 on t1.matchid=m1.matchidwhere u.usertype=1 and m1.matchid=2 

多条件查询框查询

            $sql = "select s.*, g.gradename,d.begindate from student as s left join grade as g on s.gradeid = g.gradeid
                     left join degrees as d on s.studentid = d.studentid  where d.begindate like '%$begindate%' and s.studentname like '%$name%' and workcity like'%$city%' and mobile like '%$mobile%' and comfrom like '%$comfrom%' and degreename like '%$degreename%' ";

            //echo $sql;

            $query = $this->db->query("$sql");
                        return $query->result();

==========================另一种方法，判断分析where，从而实现 多条件查询         [where 1=1 是关键，首先给个真，然后其它字段不为空则 and  条件  and 条件 ]

<span style="font-size:14px;">$sqlwhere = "where 1=1";if( strlen($begindate)!=0 ){$sqlwhere = $sqlwhere. " and studentid in (select studentid from degrees where begindate like '%$begindate%')";}if( strlen($name) != "" ){$sqlwhere = $sqlwhere. " and studentname like '%$name%'";}if( strlen($thecity) != "" ){$sqlwhere = $sqlwhere. " and thecity = '$thecity'";}if( strlen($workplace) != "" ){$sqlwhere = $sqlwhere. " and workplace like '%$workplace%'";}if( strlen($mobile) != "" ){$sqlwhere = $sqlwhere. " and mobile like '%$mobile%'";}if( strlen($comfrom) != "" ){$sqlwhere = $sqlwhere. " and province like '%$comfrom%'";}if($wptype != ""){$sqlwhere = $sqlwhere. " and wptype = $wptype";}$sql = "select * from student ".$sqlwhere;//var_dump($sql);                      $query = $this->db->query($sql);                        return $query->result();</span>

=============================================================

多表查询，包含子查询。以及查询优化的写法，（用一个查询结果当成表。与其它表进行联合

参赛人数 需要统计，并没有直接的字段值对应

team表

public function getTeamList($teamname,$intStatus,$matchid,$is_abroad){$sql = "SELECT t.teamid, t.teamname, t.status, t.is_abroad, u.userid, u.realname, u.mobile, tu.num FROM team t LEFT JOIN users u on t.userID = u.userid  LEFT JOIN  (SELECT count(*) as num, teamid FROM team_users GROUP BY teamid) tu on t.teamid = tu.teamidWHERE t.matchid = ".$matchid;if($teamname!=""){$sql = $sql." and t.teamname LIKE '%".$teamname."%' ";}if($intStatus!=""){                                        $sql = $sql." and t.status in (".$intStatus.")";}if($is_abroad!=""){$sql = $sql." and t.is_abroad in (".$is_abroad.")";}$sql = $sql." ORDER BY t.teamname";//var_dump($sql);        $query = $this->db->query($sql);        return $query->result_array();}                        

查询优化：查询字段是查询 是字段，where是where,并在下边根据不同的条件拼接where , order放最后，再进行拼接。清晰明了

(SELECT count(*) as num, teamid FROM team_users GROUP BY teamid)

为

--------------------------------------------------------------------------------------上边的应用例1  

查询最后更新的时间 （不同组的更新时间

队伍的支付记录表。 现在要从中查询每个组的最后支付时间paytime

SELECT teamid,MAX(paytime) as paytime FROM payorder GROUP BY teamid

联合查询left join 一个查询结果集的表

//----------------查询处于不同状态的俱乐部--------------------------------public function getClubList($intStatus,$matchid){         $sql = "SELECT t.teamid, t.teamname, t.status, t.sign_up,t.examine_time,t.reg_time ,p.paytime, u.userid, u.realname, u.mobile, tu.num FROM team t                   LEFT JOIN users u on t.userID = u.userid                    LEFT JOIN  (SELECT count(*) as num, teamid FROM team_users GROUP BY teamid) tu on t.teamid = tu.teamid LEFT JOIN (SELECT teamid,MAX(paytime) as paytime FROM payorder GROUP BY teamid) p on t.teamid = p.teamid                WHERE t.matchid = ".$matchid;                                                    if($intStatus!=""){                        $sql = $sql." and t.status in (".$intStatus.")";                  }                                $sql = $sql." ORDER BY t.teamname";                  $query = $this->db->query($sql);           return $query->result_array();        }