Mysql JOIN连接终极宝典

为了更好地解释着问题，如果A JOIN B，我们不妨称作：A表为连接表（也称左表），B表为被连接表（也称右表）。

总结一：

返回数据结果，永远是连接表A在左边，被连接表B在右边。

总结二：

       left   join (左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 
　　right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
　　inner join(等值连接) 只返回两个表中联结字段相等的行，即返回既在左表，又在右表中的记录。

总结三：

自连接表的查询，要注意：需要将关键条件放在左表中，连接的时候是使用左表去找右表。

（1）例如下面例子中, machine会在不同时间下产生多条记录，如何查询最新的记录呢？使用自连接。

由于CodeIgniter在左表不支持经过查询构造器get_compiled_select的子查询，而要使用sql的子查询。

public function get_readers_via_uln_room_machine_range($uln, $room_id, $machine_min, $machine_max)
{
 $sql = "(SELECT `id`, `ULN`, `LocationID`, `SerialNumber`, `LabelID`, MAX(SeverTime) as max_time
     FROM `n01`
     WHERE `uln` = '$uln'
     AND `LocationID` = '$room_id'
     GROUP BY `SerialNumber`
     ORDER BY `SeverTime` DESC) as `n01`";

 $range  = array(intval($machine_min), intval($machine_max));
 $n01_sql = "select * from `n01` where cast(LabelID AS SIGNED)>={$range[0]} AND cast(LabelID AS SIGNED)<={$range[1]}";

 $this->db->join('('.$n01_sql.') a','a.SerialNumber=n01.SerialNumber AND a.ULN=n01.ULN AND a.LocationID=n01.LocationID AND n01.max_time=a.SeverTime','inner');
 $this->db->order_by('(n01.LabelID+0)', 'ASC');
 $query = $this->db->get($sql);

 return $query->result();
}

解析一下：
对于左表，查询在当前uln和room下的不同机器SerialNumber码的最大SeverTime的记录；

对于右表，查询在$machine_min, $machine_max范围内的记录；

查询结果，用左表关联右表，关联关系为：

a.SerialNumber=n01.SerialNumber AND a.ULN=n01.ULN AND a.LocationID=n01.LocationID AND n01.max_time=a.SeverTime；

由于左表中的只有max_time是正确的数据信息，而SerialNumber是独一无二的，分组后也是正确的数据信息，而其它uln和LocationID是输入信息，所以他们也是正确数据信息，用他们去关联右边，就可以得到正确的所需要的信息。

----------------------------------------------------------------------------------------------------------------------------------------------

（2）还有一种折中方法，既可以使用CodeIgniter的查询构造器，又可以得到正确的数据。在下面的方法中，左表是正确数据，右表仅查询出和左表肯定相同的字段，所以右表不会查询出错误信息而覆盖左表，所有结果也是正确的。代码如下：

public function get_readers_via_uln_room_machine_range($uln, $room_id, $machine_min, $machine_max)
{
 $sql = $this->db
	->select("ULN, LocationID, SerialNumber, MAX(SeverTime) as max_time")
	->where("uln", $uln)
	->where("LocationID", $room_id)
    ->group_by("SerialNumber")
    ->order_by("SeverTime DESC")
	->get_compiled_select("n01");

 $range  = array(intval($machine_min), intval($machine_max));
 $n01_sql = "(select * from `n01` where cast(LabelID AS SIGNED)>={$range[0]} AND cast(LabelID AS SIGNED)<={$range[1]}) AS n01";

 $this->db->join('('.$sql.') a','a.SerialNumber=n01.SerialNumber AND a.ULN=n01.ULN AND a.LocationID=n01.LocationID AND a.max_time=n01.SeverTime','inner');
 $this->db->order_by('(n01.LabelID+0)', 'ASC');
 $query = $this->db->get($n01_sql);

 return $query->result();
}

SQL语句（无条件的）

SELECT *
FROM `n01`
INNER JOIN (
	SELECT ULN, LocationID, SerialNumber, MAX(SeverTime) as max_time FROM `n01` GROUP BY `SerialNumber` ORDER BY `SeverTime` DESC
) `a` ON a.SerialNumber=n01.SerialNumber AND a.ULN=n01.ULN AND a.LocationID=n01.LocationID AND a.max_time=n01.SeverTime
ORDER BY (n01.LabelID+0) DESC

 

为什么查询条件要分别使用在左右表中呢？其实LabelID范围的查询，也可以和uln、LocationID查询条件放在一起，结果都是正确的。

（3）最后，发现一种解决“MySQL不能分组内排序”的方法，具体可参考：https://www.cnblogs.com/userzf/p/10875470.html

SELECT * 
FROM (
	SELECT * FROM `n01` ORDER BY SeverTime DESC LIMIT 999999999
) `n01`
GROUP BY CoplSerialNumber

-- Added the sql statement 'LIMIT 999999999'

或

SELECT * 
FROM (
	SELECT * FROM `n01` GROUP BY SerialNumber, SeverTime ORDER BY SeverTime DESC
) `n01`
GROUP BY SerialNumber
ORDER BY (LabelID+0) DESC

-- Added the sql statement 'GROUP BY SerialNumber, SeverTime'