比较两个MySQL表之间的最高值(Compare Highest Values Between Two MySQL Tables)

 我有两个表search_history和parse_history，它们包含具有不同时间戳的相同案例编号的多行，当我最后一次刮时，在另一个表中，当我上次解析它时。 我试图编写一个查询，它将比较search_history表中案例编号的最高时间戳值与parse_history表中该案例编号的最高时间戳值。 如果search_history中该病例号的最近条目的时间戳高于parse_history表中该病例号的最近条目，则返回该时间戳。 
 
 到目前为止我看到的所有示例都是使用Max或groupwise max从单个表中的多个条目中获取最高值。 比较两者我找不到任何东西。 
 
 下面是我的两个表格，在下面的示例中，我需要返回案例编号4W90B2F，因为最新的search_history条目的时间戳比该案例编号的最新parse_history条目更高。 
 
search_history Table
ID  CaseNumber  TimeStamp
1   4W90B2F 2017-09-30 00:25:33
2   0DB0NGV 2017-09-30 00:15:35
3   4W90B2F 2017-10-05 00:15:44
4   0DB0NGV 2017-10-10 00:53:13
5   4W90B2F 2017-10-20 00:25:34

parse_history Table
ID  CaseNumber  TimeStamp
1   4W90B2F 2017-10-01 00:25:33
2   0DB0NGV 2017-10-02 00:15:35
3   4W90B2F 2017-10-06 00:15:44
4   0DB0NGV 2017-10-11 00:53:13
 
 SQL Fiddle链接以示例http://sqlfiddle.com/#!9/bc229f 
 
 我的尝试到目前为止已经过了 
 
SELECT sh.*
FROM search_history sh
LEFT JOIN search_history b
ON sh.CaseNumber = b.CaseNumber AND sh.Timestamp < b.Timestamp
INNER JOIN
parse_history as ph
LEFT JOIN parse_history c
ON ph.CaseNumber = c.CaseNumber AND ph.Timestamp < c.Timestamp
WHERE b.CaseNumber IS NULL AND
c.CaseNumber IS NULL
LIMIT 50

I have two tables, search_history and parse_history, that contain multiple rows of the same case number with different timestamps of when I last scraped it and in the other table when I last parsed it. I'm trying to write a query that will compare the highest timestamp value for a case number in the search_history table with the highest timestamp value for that case number in the parse_history table. If the timestamp for the most recent entry for that case number in search_history is higher than the most recent entry for that case number in the parse_history table then it is returned.
 
All the examples I've seen so far are for getting the highest values from multiple entries out of a single table using Max or groupwise max. I can't find anything on comparing the two.
 
Below are my two tables and in the example below I would need case number 4W90B2F to be returned since the most recent search_history entry has a higher timestamp than the most recent parse_history entry for that case number.
 
search_history Table
ID  CaseNumber  TimeStamp
1   4W90B2F 2017-09-30 00:25:33
2   0DB0NGV 2017-09-30 00:15:35
3   4W90B2F 2017-10-05 00:15:44
4   0DB0NGV 2017-10-10 00:53:13
5   4W90B2F 2017-10-20 00:25:34

parse_history Table
ID  CaseNumber  TimeStamp
1   4W90B2F 2017-10-01 00:25:33
2   0DB0NGV 2017-10-02 00:15:35
3   4W90B2F 2017-10-06 00:15:44
4   0DB0NGV 2017-10-11 00:53:13
 
SQL Fiddle Link to sample http://sqlfiddle.com/#!9/bc229f
 
My attempt so far times out
 
SELECT sh.*
FROM search_history sh
LEFT JOIN search_history b
ON sh.CaseNumber = b.CaseNumber AND sh.Timestamp < b.Timestamp
INNER JOIN
parse_history as ph
LEFT JOIN parse_history c
ON ph.CaseNumber = c.CaseNumber AND ph.Timestamp < c.Timestamp
WHERE b.CaseNumber IS NULL AND
c.CaseNumber IS NULL
LIMIT 50

原文：https://stackoverflow.com/questions/46976667