我的数据库有两个表
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 | |
+-----------+----------+------+-----+---------+-------+
我想要一个查询，该查询将显示 notestbl 表中给定 ID 的最后注释
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 |
+------+------+--------+------+---------------------+------------------+
只需在 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)
SELECT C.ID,
C.NAME,
C.EMAIL,
N1.ID,
N1.DATEADDED,
N1.NOTES
FROM CONTACTSTBL C
LEFT JOIN NOTESTBL N1 USING(ID)
LEFT JOIN NOTESTBL N2 ON N1.ID = N2.ID
AND N1.DATEADDED < N2.DATEADDED
WHERE N2.ID IS NULL
ORDER BY N1.DATEADDED;
也可以从这里尝试一些想法 如何查询 SQL 以获取每个用户的最新记录日期
首先，我认为您应该更改notestbl表的架构，因为它没有自己的 id 字段，而是完全依赖于contactstbl表的 id。这是糟糕的设计，应该规范化，以防止您将来:)
我建议将其更改为以下内容：
mysql> select * from notestbl;
+------+------------+------------------+---------------------+
| id | contact_id | notes | dateadded |
+------+------------+------------------+---------------------+
| 1 | 1 | 2 days ago notes | 2020-01-12 00:00:00 |
| 2 | 3 | 5 days ago notes | 2020-01-09 00:00:00 |
| 3 | 3 | 3 days ago notes | 2020-01-11 00:00:00 |
| 4 | 1 | 1 days ago notes | 2020-01-13 00:00:00 |
| 5 | 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 (select t1.id, t1.contact_id, t1.dateadded, t1.notes from notestbl t1, (select contact_id, max(dateadded) as maxdate from notestbl group by contact_id) t2 where t1.contact_id=t2.contact_id and t1.dateadded=t2.maxdate) n on c.id=n.contact_id;
+------+------+--------+------+---------------------+------------------+
| id | name | email | id | dateadded | notes |
+------+------+--------+------+---------------------+------------------+
| 1 | fran | gf@g.m | 4 | 2020-01-13 00:00:00 | 1 days ago 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 |
+------+------+--------+------+---------------------+------------------+
3 rows in set (0.00 sec)
更赏心悦目的查询表示形式：
select c.id,
c.name,
c.email,
n.id,
n.dateadded,
n.notes
from contactstbl c
left join (select t1.id,
t1.contact_id,
t1.dateadded,
t1.notes
from notestbl t1,
(select contact_id, max(dateadded) as maxdate from notestbl group by contact_id) t2
where t1.contact_id=t2.contact_id
and t1.dateadded=t2.maxdate) n
on c.id=n.contact_id;