gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-11-29 01:39:33 25 4
gpt4 key购买 nike

我有 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。

最佳答案

这是一种方法:

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;

关于mysql - 跨表获取列中的最大公共(public)值,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/29422001/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com