MySQL UNION SELECT在几列上找到MAX MAX [英] MySQL UNION SELECT on several columns to find MAX of MAX

问题描述

这是我上一个问题的延续.

假设我们有三个表.一个主表和两个id表.

Assume we have three tables. A main table and two id tables.

+-----+-----+--------------------------------------+
| cid | pid | date1      | date2      | date3      |
+-----+-----+--------------------------------------+
|  1  |  2  | NULL       | 2014-03-24 | 2014-03-24 | 
|  3  |  1  | 2014-06-13 | NULL       | NULL       | 
|  4  |  3  | NULL       | 2014-09-14 | NULL       | 
|  2  |  1  | NULL       | NULL       | 2014-08-15 | 
|  4  |  3  | 2014-01-10 | NULL       | NULL       | 
|  1  |  4  | 2014-02-15 | NULL       | NULL       | 
|  4  |  2  | NULL       | 2014-01-06 | 2014-01-12 | 
+-----+-----+------------+------------+------------+

+----+----------+     +----+--------+
| id | city     |     | id | person |
+----+----------+     +----+--------+
| 1  | 'Dallas' |     | 1  | 'John' |
| 2  | 'Berlin' |     | 2  | 'Jack' |
| 3  | 'Topeka' |     | 3  | 'Doug' |
| 4  | 'London' |     | 4  | 'Pete' |
+----+----------+     +----+--------+

好吧，现在我想做出选择，以使结果每个城市一行.该行必须包含城市，该城市的每个日期的最大值(date1，date2，date3)以及属于三个最大日期的最大值的人. 结果:

Ok, now i'd like to make a select to get one row per city in the result. The row has to contain the city, the max of each date (date1, date2, date3) of this city and the person that belongs to the max of the three max dates. Result:

+--------+--------+--------------------------------------+
| city   | person | date1      | date2      | date3      |
+--------+--------+--------------------------------------+
| Dallas | Jack   | 2014-02-15 | 2014-03-24 | 2014-03-24 | 
| Berlin | John   | NULL       | NULL       | 2014-08-15 | 
| Topeka | John   | 2014-06-13 | NULL       | NULL       | 
| London | Doug   | 2014-01-10 | 2014-09-14 | 2014-01-12 | 
+--------+--------+------------+------------+------------+

嗯...我以为那没那么难.

Mhh... I thought it would't be that difficult.

看到小提琴

推荐答案

我认为这可能有效.

select c.city, p.person, y.date1, y.date2, y.date3
  from (select x.cid, x.date1, x.date2, x.date3, greatest(ifnull(x.date1, '0000-01-01'), ifnull(x.date2, '0000-01-01'), ifnull(x.date3, '0000-01-01')) as maxdate
          from (select cid, max(date1) as date1, max(date2) as date2, max(date3) as date3
                  from main
                 group by cid) as x)
              as y
join main m 
  on m.cid = y.cid and 
  (m.date1 = y.maxdate or m.date2 = y.maxdate or m.date3 = y.maxdate)
join city c 
  on y.cid = c.id
join person p 
  on m.pid = p.id

首先创建"x"，该表是每个城市的最大日期表.然后，它会在三个日期中的最高日期处添加"y".然后，它与主表连接以找到日期最高的城市的行.然后将其连接到city和person表以获取名称而不是ID.

It starts by creating 'x' which is a table with the max dates for each city. Then it creates 'y' where it adds on the highest of the 3 dates. Then it joins with the main table to find the row for the city with the highest date. And then it joins the city and person table to get the names rather than the ids.

这篇关于MySQL UNION SELECT在几列上找到MAX MAX的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！