代码之家 › 专栏 › 技术社区 › Rodrick

如何从不同的列中选择最早的日期?

date mysql

-1

Rodrick · 技术社区 · 6 年前

例子:

Select date_1, date_2, date_3, do_something(oldest_date) from table;

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

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;

我试图按照建议使用该案例,但我得到:

Error Code: 1054. Unknown column 'date_1' in 'field list'

解决方案(我遵循了@joe stefanelli提供的解决方案和@Uuerdo的建议,谢谢):

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;

3 回复 | 直到 6 年前

Joe Stefanelli 6 年前

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;

Arnold Cross 6 年前

这是对原文的回答:

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;

Arnold Cross 6 年前

好吧,我试过了,有点复杂。我不确定这是否有效:

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;