mysql - 如何从MySQL数据库JOIN中获取最后添加的字段

Question

我的数据库有两个表

MariaDB [testnotes]> describe contactstbl;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(6)      | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| phone | varchar(20) | YES  |     | NULL    |       |
| email | varchar(40) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

MariaDB [testnotes]> describe notestbl;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| id        | int(6)   | YES  |     | NULL    |       |
| notes     | blob     | YES  |     | NULL    |       |
| dateadded | datetime | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+

我想要一个查询，该查询将显示给定 ID 的 notebl 表中的最后一条注释

contactstbl 有大约 100 条记录，即使没有注释，我也想将它们全部显示出来

MariaDB [testnotes]> select * from contactstbl;
+------+------+-------+--------+
| id   | name | phone | email  |
+------+------+-------+--------+
|    1 | fran | 12335 | gf@g.m |
|    2 | tony | 45355 | ck@g.m |
|    3 | samm | 46545 | fs@g.m |
+------+------+-------+--------+

MariaDB [testnotes]> select * from notestbl;
+------+------------------+---------------------+
| id   | notes            | dateadded           |
+------+------------------+---------------------+
|    1 | 2 days ago notes | 2020-01-12 00:00:00 |
|    3 | 5 days ago notes | 2020-01-09 00:00:00 |
|    3 | 3 days ago notes | 2020-01-11 00:00:00 |
|    1 | 1 days ago notes | 2020-01-13 00:00:00 |
|    1 | 3 days ago notes | 2020-01-11 00:00:00 |
+------+------------------+---------------------+
5 rows in set (0.00 sec)

我尝试了几个不同的查询，但似乎无法得到正确的结果。

SELECT c.id,c.name,c.email,n.id,n.dateadded,n.notes FROM contactstbl c left join notestbl n using(id) GROUP BY c.id ORDER BY n.dateadded ASC;

非常接近。

+------+------+--------+------+---------------------+------------------+
| id   | name | email  | id   | dateadded           | notes            |
+------+------+--------+------+---------------------+------------------+
|    2 | tony | ck@g.m | NULL | NULL                | NULL             |
|    3 | samm | fs@g.m |    3 | 2020-01-09 00:00:00 | 5 days ago notes |
|    1 | fran | gf@g.m |    1 | 2020-01-12 00:00:00 | 2 days ago notes |
+------+------+--------+------+---------------------+------------------+

我想看到的是:

+------+------+--------+------+---------------------+------------------+
| id   | name | email  | id   | dateadded           | notes            |
+------+------+--------+------+---------------------+------------------+
|    2 | tony | ck@g.m | NULL | NULL                | NULL             |
|    3 | samm | fs@g.m |    3 | 2020-01-11 00:00:00 | 3 days ago notes |
|    1 | fran | gf@g.m |    1 | 2020-01-13 00:00:00 | 1 days ago notes |
+------+------+--------+------+---------------------+------------------+

Answer 1

只需在 SELECT 子句中使用子查询:

SELECT 
    c.id,
    c.name,
    c.email,
    (SELECT n.id FROM notestbl n WHERE n.id=c.id ORDER BY n.dateadded DESC LIMIT 1) nid,
    (SELECT n.dateadded FROM notestbl n WHERE n.id=c.id ORDER BY n.dateadded DESC LIMIT 1) ndateadded,
    (SELECT n.notes FROM notestbl n WHERE n.id=c.id ORDER BY n.dateadded DESC LIMIT 1) nnotes
FROM 
    contactstbl c 
GROUP BY c.id 
ORDER BY ndateadded ASC;

结果:

MariaDB [test]> SELECT
    ->     c.id,
    ->     c.name,
    ->     c.email,
    ->     (SELECT n.id FROM notestbl n WHERE n.id=c.id ORDER BY n.dateadded DESC LIMIT 1) nid,
    ->     (SELECT n.dateadded FROM notestbl n WHERE n.id=c.id ORDER BY n.dateadded DESC LIMIT 1) ndateadded,
    ->     (SELECT n.notes FROM notestbl n WHERE n.id=c.id ORDER BY n.dateadded DESC LIMIT 1) nnotes
    -> FROM
    ->     contactstbl c
    -> GROUP BY c.id
    -> ORDER BY ndateadded ASC;
+----+------+--------+------+---------------------+------------------+
| id | name | email  | nid  | ndateadded          | nnotes           |
+----+------+--------+------+---------------------+------------------+
|  2 | tony | ck@g.m | NULL | NULL                | NULL             |
|  3 | sam  | fs@g.  |    3 | 2020-01-11 00:00:00 | 3 days ago notes |
|  1 | fran | gf@g.m |    1 | 2020-01-13 00:00:00 | 1 days ago notes |
+----+------+--------+------+---------------------+------------------+
3 rows in set (0.07 sec)