MYSQL 选择最大日期内联接语句

标签: MySQL
发布时间: 2015/6/11 19:08:22
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我试图返回大量记录历史位置

我所拥有的是:

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 分钟,解析并返回数据,如:

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

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

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 和位置。

任何人都可以看到自己在做什么错了吗?我感谢的帮助。

解决方法 1:

像这种事情......

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

其核心是这...

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;
赞助商