文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
社区首页 >问答首页 >如何从不同的列中选择最早的日期？

问如何从不同的列中选择最早的日期？
EN

Stack Overflow用户
提问于 2018-12-06 05:49:14
回答 3查看 52关注 0票数 -1

我知道我可以使用min( date )来查找列之间最早的日期，但是如何在3个不同的列之间选择最早的日期呢？

示例：

代码语言：javascript
复制
Select date_1, date_2, date_3, do_something(oldest_date) from table;

编辑:实际上我的查询有点复杂。

代码语言：javascript
复制
SELECT 
    (SELECT 
            IF(test.OLD = 1, DATE_FORMAT(test.DATE_EXPIRATION, '%d-%m-%Y'), IF(revalidation.recognized = 0, DATE_FORMAT(DATE_ADD(revalidation.DATE_test, INTERVAL revalidation.VALIDITY YEAR), '%d-%m-%Y'), IF(test.DATE_EXPIRATION = ''
                    OR test.DATE_EXPIRATION IS NULL, DATE_FORMAT(DATE_ADD(test.DATE_test, INTERVAL test.VALIDITY YEAR), '%d-%m-%Y'), DATE_FORMAT(test.DATE_EXPIRATION, '%d-%m-%Y')))) AS VENCIMIENTO
        FROM
            eva_compet revalidation
        LEFT JOIN eva_compet test ON (revalidation.recognized = test.EVA_LAB_ID)
        WHERE
            revalidation.TYPO_EVA_ID = 1
                AND revalidation.LABORAL_ID = cl.laboral_id
        LIMIT 1) AS DATE_1,
    (SELECT 
            IF(test.OLD = 1, DATE_FORMAT(test.DATE_EXPIRATION, '%d-%m-%Y'), IF(revalidation.recognized = 0, DATE_FORMAT(DATE_ADD(revalidation.DATE_test, INTERVAL revalidation.VALIDITY YEAR), '%d-%m-%Y'), IF(test.DATE_EXPIRATION = ''
                    OR test.DATE_EXPIRATION IS NULL, DATE_FORMAT(DATE_ADD(test.DATE_test, INTERVAL test.VALIDITY YEAR), '%d-%m-%Y'), DATE_FORMAT(test.DATE_EXPIRATION, '%d-%m-%Y')))) AS VENCIMIENTO
        FROM
            eva_compet revalidation
        LEFT JOIN eva_compet test ON (revalidation.recognized = test.EVA_LAB_ID)
        WHERE
            revalidation.TYPO_EVA_ID = 2
                AND revalidation.LABORAL_ID = cl.laboral_id
        LIMIT 1) AS DATE_2,
    (SELECT 
            IF(test.OLD = 1, DATE_FORMAT(test.DATE_EXPIRATION, '%d-%m-%Y'), IF(revalidation.recognized = 0, DATE_FORMAT(DATE_ADD(revalidation.DATE_test, INTERVAL revalidation.VALIDITY YEAR), '%d-%m-%Y'), IF(test.DATE_EXPIRATION = ''
                    OR test.DATE_EXPIRATION IS NULL, DATE_FORMAT(DATE_ADD(test.DATE_test, INTERVAL test.VALIDITY YEAR), '%d-%m-%Y'), DATE_FORMAT(test.DATE_EXPIRATION, '%d-%m-%Y')))) AS VENCIMIENTO
        FROM
            eva_compet revalidation
        LEFT JOIN eva_compet test ON (revalidation.recognized = test.EVA_LAB_ID)
        WHERE
            revalidation.TYPO_EVA_ID = 3
                AND revalidation.LABORAL_ID = cl.laboral_id
        LIMIT 1) AS DATE_3,
        CASE WHEN DATE_1 <= DATE_2 AND DATE_1 <= DATE_3 THEN DATE_1
            WHEN DATE_2 <= DATE_1 AND DATE_2 <= DATE_3 THEN DATE_2
            ELSE DATE_3
        END AS TERMINO
FROM
    table
WHERE
    table_id = 1001;

我试着按照建议使用这个案例，但我得到了：

代码语言：javascript
复制
Error Code: 1054. Unknown column 'date_1' in 'field list'

解决方案(我遵循了@joe-stefanelli提供的解决方案和@Uueerdo的建议，谢谢)：

代码语言：javascript
复制
SELECT 
    DATE_1, 
    DATE_2, 
    DATE_3, 
    CASE WHEN DATE_1 <= DATE_2 AND DATE_1 <= DATE_3 THEN DATE_1
                WHEN DATE_2 <= DATE_1 AND DATE_2 <= DATE_3 THEN DATE_2
                ELSE DATE_3
            END AS OLDEST_DATE 
            FROM
(SELECT 
        (SELECT 
                IF(test.OLD = 1, DATE_FORMAT(test.DATE_EXPIRATION, '%d-%m-%Y'), IF(revalidation.recognized = 0, DATE_FORMAT(DATE_ADD(revalidation.DATE_test, INTERVAL revalidation.VALIDITY YEAR), '%d-%m-%Y'), IF(test.DATE_EXPIRATION = ''
                        OR test.DATE_EXPIRATION IS NULL, DATE_FORMAT(DATE_ADD(test.DATE_test, INTERVAL test.VALIDITY YEAR), '%d-%m-%Y'), DATE_FORMAT(test.DATE_EXPIRATION, '%d-%m-%Y')))) AS VENCIMIENTO
            FROM
                eva_compet revalidation
            LEFT JOIN eva_compet test ON (revalidation.recognized = test.EVA_LAB_ID)
            WHERE
                revalidation.TYPO_EVA_ID = 1
                    AND revalidation.LABORAL_ID = cl.laboral_id
            LIMIT 1) AS DATE_1,
        (SELECT 
                IF(test.OLD = 1, DATE_FORMAT(test.DATE_EXPIRATION, '%d-%m-%Y'), IF(revalidation.recognized = 0, DATE_FORMAT(DATE_ADD(revalidation.DATE_test, INTERVAL revalidation.VALIDITY YEAR), '%d-%m-%Y'), IF(test.DATE_EXPIRATION = ''
                        OR test.DATE_EXPIRATION IS NULL, DATE_FORMAT(DATE_ADD(test.DATE_test, INTERVAL test.VALIDITY YEAR), '%d-%m-%Y'), DATE_FORMAT(test.DATE_EXPIRATION, '%d-%m-%Y')))) AS VENCIMIENTO
            FROM
                eva_compet revalidation
            LEFT JOIN eva_compet test ON (revalidation.recognized = test.EVA_LAB_ID)
            WHERE
                revalidation.TYPO_EVA_ID = 2
                    AND revalidation.LABORAL_ID = cl.laboral_id
            LIMIT 1) AS DATE_2,
        (SELECT 
                IF(test.OLD = 1, DATE_FORMAT(test.DATE_EXPIRATION, '%d-%m-%Y'), IF(revalidation.recognized = 0, DATE_FORMAT(DATE_ADD(revalidation.DATE_test, INTERVAL revalidation.VALIDITY YEAR), '%d-%m-%Y'), IF(test.DATE_EXPIRATION = ''
                        OR test.DATE_EXPIRATION IS NULL, DATE_FORMAT(DATE_ADD(test.DATE_test, INTERVAL test.VALIDITY YEAR), '%d-%m-%Y'), DATE_FORMAT(test.DATE_EXPIRATION, '%d-%m-%Y')))) AS VENCIMIENTO
            FROM
                eva_compet revalidation
            LEFT JOIN eva_compet test ON (revalidation.recognized = test.EVA_LAB_ID)
            WHERE
                revalidation.TYPO_EVA_ID = 3
                    AND revalidation.LABORAL_ID = cl.laboral_id
            LIMIT 1) AS DATE_3,
            CASE WHEN DATE_1 <= DATE_2 AND DATE_1 <= DATE_3 THEN DATE_1
                WHEN DATE_2 <= DATE_1 AND DATE_2 <= DATE_3 THEN DATE_2
                ELSE DATE_3
            END AS TERMINO
    FROM
        table
    WHERE
        table_id = 1001)
        AS table_2;
mysql
date
EN

回答 3

Stack Overflow用户

回答已采纳

发布于 2018-12-06 05:58:40

代码语言：javascript
复制
SELECT CASE WHEN date_1 <= date_2 AND date_1 <= date_3 THEN date_1
            WHEN date_2 <= date_1 AND date_2 <= date_3 THEN date_2
            ELSE date_3
       END AS oldest_date
    FROM table;
票数 1
EN

Stack Overflow用户

发布于 2018-12-06 06:38:14

这回答了最初的帖子：

select date_1, date_2, date_3, do_something(a) from table, (select least(date_1, date_2, date_3) a from table) b;

我不会试图弄清楚编辑的复杂性。

如果您想要的只是函数的结果，则如下所示：

select do_something(a) from (select least(date_1, date_2, date_3) a from table) b;

票数 0
EN

Stack Overflow用户

发布于 2018-12-06 07:14:14

好吧，我试了试它的复杂性。我不确定这是否会起作用：

SELECT DATE_1, DATE_2, DATE_3, do_something(a) FROM b, ( SELECT LEAST(DATE_1, DATE_2, DATE_3) a FROM ( SELECT (...) as DATE_1, (...) as DATE_2, (...) as DATE_3 ) FROM table WHERE table_id = 1001 ) AS b ) AS c;

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/53641269

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档