mysql - 跨表获取列中的最大公共(public)值

Question

我有 4 个表(比如 A、B、C 和 D)，所有表都包含“日期”列。我需要在所有四个表中找到最大的公共(public)日期值。即，存在于所有四个表中的日期的最大值。我该怎么做？

目前，我正在寻找所有四个表的 MAX 日期值的 MIN，但如果 MIN 存在于一个表中但不存在于第二个表中，则此方法失败。

下面是一个让事情更清楚的例子:

A.date
------
2015-03-31
2015-03-30
2015-03-29
2015-03-27

B.date
------
2015-03-30
2015-03-29
2015-03-28
2015-03-27

C.date
------
2015-03-29
2015-03-27
2015-03-26
2015-03-25

D.date
------
2015-03-28
2015-03-27
2015-03-26
2015-03-25

我为找到最常见日期所做的工作是:

SELECT MIN(max_date) FROM (
SELECT MAX(date) AS max_date FROM A
UNION
SELECT MAX(date) AS max_date FROM B
UNION
SELECT MAX(date) AS max_date FROM C
UNION
SELECT MAX(date) AS max_date FROM D
) T;

这给了我 2015-03-28，但后来我意识到有些表可能根本没有这个日期。我实际想要获取的日期是 2015-03-27。

Answer 1

这是一种方法:

select date
from (select date, 'a' as which from a union all
      select date, 'b' as which from b union all
      select date, 'c' as which from c union all
      select date, 'd' as which from d
     ) x
group by date
having count(distinct which) = 4
order by date desc
limit 1;

以下版本可能会执行得更好一些，特别是如果您在每个表中都有一个 date 的索引:

select date
from (select distinct date, 'a' as which from a union all
      select distinct date, 'b' as which from b union all
      select distinct date, 'c' as which from c union all
      select distinct date, 'd' as which from d
     ) x
group by date
having count(*) = 4
order by date desc
limit 1;