MYSQL在联接语句中选择MAX日期

本文介绍了MYSQL在联接语句中选择MAX日期的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试返回记录编号的历史位置

I'm trying to return a record number's historic locations

我所拥有的是:

SELECT l.location, t.transaction_id, t.date_modified FROM transactions as t INNER JOIN ( SELECT t1.received_id, t1.transaction_id, t1.date_modified FROM ( SELECT received_id, MAX(date_modified) as maxmodify FROM transactions GROUP BY received_id) as max_record JOIN transactions as t1 ON (t1.received_id =max_record.received_id) ) as whatever INNER JOIN locations as l ON l.location_id = t.location_id INNER JOIN received as r ON r.received_id = t.received_id WHERE t.received_id='1782' ORDER BY t.date_modified DESC

这大约需要1分钟来解析，并返回如下数据:

This takes about 1 min to parse and returns data like:

T-E1A 67294 2013-05-29 14:05:30 T-E1A 67293 2013-05-29 14:05:30 T-E1A 67294 2013-05-29 14:05:30 T-E1A 67293 2013-05-29 14:05:30 T-E1A 67294 2013-05-29 14:05:30 T-E1A 67293 2013-05-29 14:05:30 T-E1A 67294 2013-05-29 14:05:30

我真正希望看到的是类似这样的查询中的数据:

What I'm really expecting to see is data like from a query like this:

SELECT l.location, t.transaction_id, t.date_modified FROM transactions as t JOIN locations as l ON l.location_id = t.location_id JOIN received as r ON r.received_id = t.received_id WHERE t.received_id='1782' ORDER BY t.date_modified DESC

返回哪个

T-E1A 67290 2013-05-29 13:58:26 T-E1A 67289 2013-05-29 13:58:26 ADJUST 67283 2013-04-26 11:33:54 ADJUST 67284 2013-04-26 11:33:54 ST10 67279 2013-04-26 09:52:41 ST10 67278 2013-04-26 09:52:13 ST10 67277 2013-04-26 09:50:58 ST10 67276 2013-04-26 09:50:20 SH3 67274 2013-04-26 09:49:39

第二个查询更好，但我真的只想显示每个记录ID和位置的上次修改时间.

This second query is better but I really want to only show the last modified time for each record id and location.

有人可以看到我在做什么吗?感谢您的帮助.

Can anybody see what I'm doing wrong? I appreciate the help.

推荐答案

类似这样的事情...

Something like this...

SELECT t1.received_id , t1.transaction_id , t1.date_modified , l.location FROM transactions t1 JOIN ( SELECT received_id, MAX(date_modified) maxmodify FROM transactions GROUP BY received_id) max_record ON max_record.received_id = t1.received_id AND max_record.maxmodify = t1.date_modified JOIN locations l ON l.location_id = t1.location_id JOIN received r ON r.received_id = t1.received_id WHERE t1.received_id = '1782' ORDER BY t1.date_modified DESC

它的内核就是这个...

the kernel of which is this...

SELECT x.* FROM my_table x JOIN (SELECT id,MAX(thing) max_thing FROM my_table GROUP BY id) y ON y.id = x.id AND y.max_thing = x.thing;