php - 左连接 MySql

Question

我一直在关注这个 Left Join 答案，但无法使我的工作

MYSQL Left Join last row of result

我有两张 table

leicester

count   area_id  section   toberth
  1        LR       WL       300
  2        LR       WL       301
  3        ER       SN       300


berthmove

count   area_id    timemove    description    toberth
  1        LR        124          6R43           300
  2        LR        126          5RT4           300
  3        TR        128          6TF7           310
  4        LR        121          6TT3           301
  5        LR        122          5RR7           301
  6        TR        127          7YY9           300

这是我的代码

  $stmt=$mysql_link->prepare("SELECT  l.*,b.* 
     FROM leicester AS l
     LEFT JOIN berthmove AS b ON l.toberth=b.toberth WHERE l.area_id=b.area_id AND l.section="WL"
     LEFT JOIN berthmove AS b2 ON l.toberth=b2.toberth AND b.timemove<b2.timemove
     WHERE b2.timemove is NULL LIMIT 1 ");      
     $stmt->execute();  

我试图以描述 5RT4 和 5RR7 结束，即 berthmove 表中的第 2 行和第 5 行。即从表“leicester”中选择泊位 300 和 301(即它们位于 WL 部分)，然后查找针对 300 和 301 的描述。但它只能是每个泊位的最新描述(基于 timemove)。还需要检查area_id是否相同。

请帮忙

这里是获取

   foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $berths)
     {
     echo "hello";
     $selectberth=$berths['toberth'];
     $description=$berths['descr'];
     echo $description;
     echo $selectberth;
     }

编辑

更新了代码以反射(reflect) WHERE 命令的更改和解析问题

     $stmt=$mysql_link->prepare("SELECT  l.*,b.* 
     FROM leicester AS l
     LEFT JOIN berthmove AS b ON l.toberth=b.toberth
     LEFT JOIN berthmove AS b2 ON l.toberth=b2.toberth AND b.timemove<b2.timemove
     WHERE l.area_id=b.area_id AND l.section='WL' AND b2.timemove is NULL LIMIT 1 ");       
     $stmt->execute();      

Answer 1

不要通过将 berthmove 连接到自身来查找每个组具有 timemove 最大值的记录，而是尝试使用子查询，然后使用内部联接来查找所有相关信息:

SELECT l.*, b2.*
FROM leicester l
INNER JOIN 
(SELECT area_id, toberth, max(timemove) as timemove
    FROM berthmove
    GROUP BY area_id, toberth) AS b1
ON l.area_id = b1.area_id AND l.toberth = b1.toberth
INNER JOIN berthmove b2
ON b1.area_id = b2.area_id
AND b1.toberth = b2.toberth
AND b1.timemove = b2.timemove
WHERE l.section = 'WL';