mysql - 当从子表中获取最新行时，MySQL 对 GROUP BY 和 JOIN 子查询返回不同的结果

Question

我有两个处于一对多关系的表。父表(名为 Inquiry)中的一行可以有 0 或 N 行子表(名为 InqNotes)。

我想从父表中获取某些行以及从子表中获取最新的相应行(如果有)。我尝试了以下 SQL 查询:

答:

select i.*, n.LastUpdated as NotesTs, n.Notes, n.LoginId as NotesBy 
from MYDB.Inquiries i
LEFT outer join MYDB.InqNotes n on n.InquiryId=i.Id 
and n.Id = (select max(n2.Id) from MYDB.InqNotes n2 where n2.InquiryId=i.Id);

B:

select max(n.LastUpdated) as NotesTs, n.Notes, n.LoginId as NotesBy, 
i.InquiryNo, i.Status, i.InquiryType, i.ClientName, i.PhoneNo, i.Id
from MYDB.Inquiries i
LEFT outer join MYDB.InqNotes n on n.InquiryId=i.Id 
group by n.LastUpdated, n.Notes, n.LoginId, i.InquiryNo, i.Status, i.InquiryType, i.ClientName, i.PhoneNo, i.Id

我期望上述两个查询返回同一组记录。不知何故，他们返回了不同的结果。 A 中的查询返回的记录比 B 返回的记录少(即按案例分组)。可能我忽略了一些非常简单的事情，但我无法理解为什么查询会出现这种行为。如果有人指出我在这些查询中所做的/预期的错误，我们将不胜感激。

Answer 1

数据的简化版本:-

drop table if exists inquiries,inqnotes;

create table inquiries
(id int);

create table inqnotes
(id int,inquiryid int,lastupdated date);

insert into inquiries values
(1),(2);

insert into inqnotes values
(1,1,'2019-01-01'),(2,1,'2019-02-01');

select i.*, n.LastUpdated as NotesTs 
from Inquiries i
LEFT outer join InqNotes n on n.InquiryId=i.Id 
and n.Id = (select max(n2.Id) from InqNotes n2 where n2.InquiryId=i.Id);

+------+------------+
| id   | NotesTs    |
+------+------------+
|    1 | 2019-02-01 |
|    2 | NULL       |
+------+------------+
2 rows in set (0.00 sec)

select max(n.LastUpdated) as NotesTs, i.Id
from Inquiries i
LEFT outer join InqNotes n on n.InquiryId=i.Id 
group by n.lastupdated, i.Id;

+------------+------+
| NotesTs    | Id   |
+------------+------+
| NULL       |    2 |
| 2019-01-01 |    1 |
| 2019-02-01 |    1 |
+------------+------+
3 rows in set (0.00 sec)

这显然是错误的。由于 n.lastupdated 上的分组，这是错误的。

尝试删除此分组

select max(n.LastUpdated) as NotesTs, i.Id
from Inquiries i
LEFT outer join InqNotes n on n.InquiryId=i.Id 
group by i.Id;
+------------+------+
| NotesTs    | Id   |
+------------+------+
| 2019-02-01 |    1 |
| NULL       |    2 |
+------------+------+
2 rows in set (0.00 sec)