是的，它似乎被回答了多次，但我尝试的一切都失败了。
最相似的stackoverflow的帖子是肯定的：在sql中为每个ID选择重复多次的最新日期的行但这里的主要区别是，我需要在之前执行了一些左联接之后进行分组。
情况如下：
我有3个表(transaction、support和transaction_support，链接前面的2个表(
create TABLE `transaction`
(
id INT,
date_time DATE,
notes TEXT,
PRIMARY KEY (id)
);
create TABLE `support`
(
id int,
support_number int ,
PRIMARY KEY (id)
);
create TABLE `transaction_support`
(
id INT,
transaction_id int,
support_id int,
PRIMARY KEY (id),
FOREIGN KEY (transaction_id) REFERENCES transaction(id),
FOREIGN KEY (support_id) REFERENCES support(id)
);
INSERT INTO `support` values (1, 1111);
INSERT INTO `support` values (2, 2222);
INSERT INTO `support` values (3, 3333);
INSERT INTO `transaction` values (1, '1996-06-28 00:00:00', 'Old data, we shouln''t see it');
INSERT INTO `transaction` values (2, '1996-07-16 00:00:00', 'Old data, we shouln''t see it');
INSERT INTO `transaction` values (3, '2001-04-10 00:00:00', 'Old data, we shouln''t see it');
INSERT INTO `transaction` values (4, '2001-05-14 00:00:00', 'Lastest data from Danny');
INSERT INTO `transaction` values (5, '2001-05-14 00:00:00', 'Lastest data from John');
INSERT INTO `transaction` values (6, '2001-04-10 00:00:00', 'Old data, we shouln''t see it');
INSERT INTO `transaction_support` values (487131, 1, 2);
INSERT INTO `transaction_support` values (488504, 2, 2);
INSERT INTO `transaction_support` values (751126, 3, 2);
INSERT INTO `transaction_support` values (758188, 4, 2);
INSERT INTO `transaction_support` values (4444, 5, 3);
INSERT INTO `transaction_support` values (4445, 6, 3);
这里有一个请求尝试：
SELECT s.id AS s_id, t.*, MAX(t.date_time) AS `this date is good`
FROM support AS s
LEFT JOIN transaction_support AS ts ON ts.support_id = s.id
LEFT JOIN transaction AS t ON ts.transaction_id = t.id
GROUP BY ts.support_id
再次尝试子查询：
SELECT s.id as support_id, t.*, sub.*
FROM support AS s
LEFT JOIN transaction_support AS ts ON ts.support_id = s.id
LEFT JOIN transaction AS t ON ts.transaction_id = t.id
LEFT JOIN (
SELECT ts.support_id AS `sub_support_id`,
t.id AS `sub_transaction_id`,
MAX(t.date_time) AS `sub_last_date`
FROM transaction_support AS ts
LEFT JOIN transaction AS t ON ts.transaction_id = t.id
GROUP BY ts.support_id
) sub ON ts.support_id = sub.sub_support_id AND t.date_time = sub.sub_last_date
GROUP BY s.id
预期结果为：
|support_id | transaction_id | transaction_notes | transaction_date|
|-----------|----------------|-------------------------|-----------------|
| 1 | null | null | null |
| 2 | 4 | Lastest data from Danny | 2001-05-14 |
| 3 | 5 | Lastest data from John | 2001-05-14 |
我尝试了许多请求，包括子查询和不包括子查询，但到目前为止，当我"分组依据"；支持ID。
但我很确定我需要一个子查询。。。
这是一把小提琴：http://sqlfiddle.com/#！9/adc611/20
我尝试过的其他一些类似的帖子：
- 分组依据最大值(日期(
- SQL仅选择列上具有最大值的行
- MySQL select MAX(datetime(未返回最大值
如果有人能帮我找出解决方案。。。谢谢！：(
对于您的MySql版本，没有简单的解决方案
您可以使用NOT EXISTS为每个support_id获取最新date_time的数据，并将support连接到该结果集：
SELECT s.id AS support_id,
x.id AS transaction_id,
x.notes AS transaction_notes,
x.date_time AS transaction_date
FROM support AS s
LEFT JOIN (
SELECT ts.support_id, t.id, t.notes, t.date_time
FROM transaction_support ts INNER JOIN transaction t
ON ts.transaction_id = t.id
WHERE NOT EXISTS (
SELECT 1
FROM transaction_support ts2 INNER JOIN transaction t2
ON ts2.transaction_id = t2.id
WHERE ts2.support_id = ts.support_id AND t2.date_time > t.date_time
)
) AS x ON x.support_id = s.id
请参阅演示
结果：
> support_id | transaction_id | transaction_notes | transaction_date
> ---------: | -------------: | :---------------------- | :---------------
> 1 | null | null | null
> 2 | 4 | Lastest data from Danny | 2001-05-14
> 3 | 5 | Lastest data from John | 2001-05-14
如果您想要每个支持的最新事务，有一个选项在left join:的on子句中使用子查询进行筛选
select s.*, t.*
from support s
left join (
select t.*, ts.support_id
from transaction_support ts
inner join transaction t
on t.id = ts.transaction_id
and t.date_time = (
select max(t1.date_time)
from transaction_support ts1
inner join transaction t1 on t1.id = ts1.transaction_id
where ts1.support_id = ts.support_id
)
) t on s.id = t.support_id