我有两个一对多关系的表。父表中的行(名为"查询"(可以有零或 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);
乙:
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 返回的记录少(即按大小写分组(。可能是我忽略了一些非常简单的东西,但我无法理解为什么查询的这种行为。任何指向我正在做什么/期望这些查询错误的指针将不胜感激。
数据的简化版本:-
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.lastupdate上的分组。
尝试删除此分组
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)