问MySQL选择、子查询等

EN

-- Create a temporary singular table of the shared fields.
-- Ideally, the actual table probably should've looked something like this.
DROP TEMPORARY TABLE IF EXISTS tmpAll;
CREATE TEMPORARY TABLE `tmpAll`
SELECT ShortName, LongName, `Year` FROM table2016
UNION SELECT ShortName, LongName, `Year` FROM table2015
UNION SELECT ShortName, LongName, `Year` FROM table2014
UNION SELECT ShortName, LongName, `Year` FROM table2013
;

-- Find the most recent years for each name.
-- This could have just been a subquery of the next/last query; but...
-- MySQL does not support queries that reference a TEMPORARY table more than once.
DROP TEMPORARY TABLE IF EXISTS tmpRecents;
CREATE TEMPORARY TABLE `tmpRecents`
SELECT Shortname, MAX(`Year`) AS `Year` 
FROM tmpAll
GROUP BY ShortName
;

-- Get the tmpAll records for the most recent year of each ShortName
SELECT tmpAll.*
FROM tmpAll INNER JOIN `tmp` USING (ShortName, `Year`)
;

-- You can DROP the temporary tables here, or 
-- they will just go away when the connection is closed.