MySQL.从第二个表中选择最后一条记录(按创建时间） - MySQL. Select last record (by created time) from second table

MySQL. Select last record (by created time) from second table

我有两个表通过userPhone链接。

表用户

+-----------+-------+----------+--------+
| userPhone |  name | address  |  car   |
+-----------+-------+----------+--------+
| 096111111 | Bill  |  adr1    | {json} |
+-----------+-------+----------+--------+
| 097333333 | Max   |  adr2    | {json} |
+-----------+-------+----------+--------+
| 098888888 | Denis |  adr3    | {json} |
+-----------+-------+----------+--------+

表订单

+-----------+---------+-------+-------+------------+
| userPhone | orderID | title | Descr | createdTS  |
+-----------+---------+-------+-------+------------+
| 096111111 | 59      | ttl1  | qqqq  | 1444999740 |
+-----------+---------+-------+-------+------------+
| 096111111 | 58      | ttl2  | wwww  | 1444999650 |
+-----------+---------+-------+-------+------------+
| 096111111 | 56      | ttl3  | rrrrr | 1444999600 |
+-----------+---------+-------+-------+------------+
| 096111111 | 57      | ttl4  | ttttt | 1444999540 |
+-----------+---------+-------+-------+------------+

我正在使用这样的请求，它运行良好：

SELECT
      `users`.`userPhone`,
      `users`.`name`,
      `users`.`address`,
      `users`.`car`,
      (SELECT `title` FROM `orders` WHERE `orders`.`userPhone` = `users`.`userPhone` AND `orders`.`orderTS` < NOW()  ORDER BY `orders`.`createdTS` DESC LIMIT 1 ) as `title`,
      (SELECT `descr` FROM `orders` WHERE `orders`.`userPhone` = `users`.`userPhone` AND `orders`.`orderTS` < NOW()  ORDER BY `orders`.`createdTS` DESC LIMIT 1 ) as `descr`
FROM
              `users`
               LEFT JOIN `orders` USING (`userPhone`)
WHERE 
               `users`.`userPhone` like '%1111%';

但我认为额外的选择效果很慢。有没有更好更快的方法来解决它？

如何使用另一个请求（可能没有额外的SELECT）获得相同的结果。

一个语法正确的JOIN将为您完成所有这些，而不需要内部SELECT，而且速度更快。

SELECT
      `users`.`userPhone`,
      `users`.`name`,
      `users`.`address`,
      `users`.`car`,
      `orders`.`title`,
      `orders`.`desc`
FROM `users`
     JOIN `orders` ON `orders`.`phone` = `users`.`phone`
WHERE 
      `users`.`userPhone` like '%1111%'
  AND `orders`.`orderTS` < NOW() 
ORDER BY `orders`.`orderTS` DESC
LIMIT 1;

如果您想要所有这些记录，那么只需删除LIMIT 1

如果使用内部SELECT ，则不需要JOIN或LEFT JOIN任何表

试试这个

SELECT
      `users`.`userPhone`,
      `users`.`name`,
      `users`.`address`,
      `users`.`car`,
      (SELECT `title` FROM `orders` WHERE `orders`.`userPhone` = `users`.`userPhone` AND `orders`.`orderTS` < NOW()  ORDER BY `orders`.`createdTS` DESC LIMIT 1 ) as `title`,
      (SELECT `descr` FROM `orders` WHERE `orders`.`userPhone` = `users`.`userPhone` AND `orders`.`orderTS` < NOW()  ORDER BY `orders`.`createdTS` DESC LIMIT 1 ) as `descr`
FROM
              `users`
WHERE 
               `users`.`userPhone` like '%1111%';

这应该是更快的