mysql - 如何从不同的列中选择最旧的日期？

Question

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

例子：

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;

我尝试按照建议使用 CASE，但我得到：

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

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

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;

标签： mysqldate

Answer 1

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;