mysql - SQL 获取列以匹配 CASE 中的多个条件

Question

提供以下 SQL 片段:

CASE
    WHEN (YEAR('2018-12-31') - YEAR(`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') < DATE_FORMAT(`StartDateTCDR`, '%m%d')) >= 30) THEN '>= 30 (inclusive)'
    WHEN (YEAR('2018-12-31') - YEAR(`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') < DATE_FORMAT(`StartDateTCDR`, '%m%d')) >= 20) THEN '>= 20 (inclusive)'
    WHEN (YEAR('2018-12-31') - YEAR(`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') < DATE_FORMAT(`StartDateTCDR`, '%m%d')) >= 10) THEN '>= 10 (inclusive)'
    ELSE '< 10'
END

(完整的查询如下，上面的代码片段只是为了突出问题)

以上当前的独占匹配结果:

 (30 <= date_diff) = '>= 30'
 (20 <= date_diff <= 30) = '>= 20'
 (10 <= date_diff <= 20) = '>= 10'
 (date_diff < 10) = '< 10'

但我需要的是包容性匹配:

 (30 <= date_diff <= 0) = '>= 30'
 (20 <= date_diff <= 0) = '>= 20'
 (10 <= date_diff <= 0) = '>= 10'
 (date_diff < 10) = '< 10'

因此 - 如果记录超过 30 年，则必须将其分类为 >= 10、>= 20 以及 >= 30 ，等等。

完整查询

    SELECT
        COUNT(*) AS `RecordCount`, `ExistingPlanned`, `ResponsibleOrg`, `StartDateTCDR`, `EndDateTCDR`, `SpecificEndDate`, `ResponsibleOrgOut`,
        (CASE
            WHEN (`EndDateTCDR` < '1001-01-01' AND `SpecificEndDate` = 0) THEN '2018-12-31'
            WHEN (`EndDateTCDR` < '1001-01-01' AND `SpecificEndDate` = 1) THEN '-'
            ELSE `EndDateTCDR`
        END) AS `EndDate`,
        (CASE
            WHEN (`StartDateTCDR` < '1001-01-01') THEN '-'
            ELSE `StartDateTCDR`
        END) AS `StartDate`,
        (CASE
            WHEN (`StartDateTCDR` < '1001-01-01') THEN '-'
            WHEN (`StartDateTCDR` > '1001-01-01' AND `EndDateTCDR` < '1001-01-01' AND `SpecificEndDate` = 1) THEN '-'
            WHEN (`StartDateTCDR` > '1001-01-01' AND `EndDateTCDR` < '1001-01-01' AND `SpecificEndDate` = 0) THEN (
                CASE
                    WHEN (YEAR('2018-12-31') - YEAR(`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') < DATE_FORMAT(`StartDateTCDR`, '%m%d')) >= 30) THEN '>= 30 (inclusive)'
                    WHEN (YEAR('2018-12-31') - YEAR(`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') < DATE_FORMAT(`StartDateTCDR`, '%m%d')) >= 20) THEN '>= 20 (inclusive)'
                    WHEN (YEAR('2018-12-31') - YEAR(`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') < DATE_FORMAT(`StartDateTCDR`, '%m%d')) >= 10) THEN '>= 10 (inclusive)'
                    ELSE '< 10'
                END
            )
            WHEN (`StartDateTCDR` > '1001-01-01' AND `EndDateTCDR` > '1001-01-01') THEN (
                CASE
                    WHEN (YEAR(`EndDateTCDR`) - YEAR(`StartDateTCDR`) - (DATE_FORMAT(`EndDateTCDR`, '%m%d') < DATE_FORMAT(`StartDateTCDR`, '%m%d')) >= 30) THEN '>= 30 (inclusive)'
                    WHEN (YEAR(`EndDateTCDR`) - YEAR(`StartDateTCDR`) - (DATE_FORMAT(`EndDateTCDR`, '%m%d') < DATE_FORMAT(`StartDateTCDR`, '%m%d')) >= 20) THEN '>= 20 (inclusive)'
                    WHEN (YEAR(`EndDateTCDR`) - YEAR(`StartDateTCDR`) - (DATE_FORMAT(`EndDateTCDR`, '%m%d') < DATE_FORMAT(`StartDateTCDR`, '%m%d')) >= 10) THEN '>= 10 (inclusive)'
                    ELSE '< 10'
                END
            )
        END) AS `TCDRLength`,
        (CASE
            WHEN (`StartDateTCDR` < '1001-01-01') THEN '-'
            WHEN (`StartDateTCDR` > '1001-01-01' AND `EndDateTCDR` < '1001-01-01' AND `SpecificEndDate` = 1) THEN '-'
            WHEN (`StartDateTCDR` > '1001-01-01' AND `EndDateTCDR` < '1001-01-01' AND `SpecificEndDate` = 0) THEN (
                ROUND((DATEDIFF('2018-12-31', `StartDateTCDR`) / 365), 2)
            )
            WHEN (`StartDateTCDR` > '1001-01-01' AND `EndDateTCDR` > '1001-01-01') THEN (
                ROUND((DATEDIFF(`EndDateTCDR`, `StartDateTCDR`) / 365), 2)
            )
        END) AS `ActualLength`
    FROM `data_records`
    LEFT JOIN `RespOrgLUT`
        ON `ResponsibleOrgIn` = `ResponsibleOrg`
    GROUP BY `ExistingPlanned`, `ResponsibleOrg`, `StartDateTCDR`, `EndDateTCDR`, `SpecificEndDate`, `ResponsibleOrgOut`, `StartDate`, `EndDate`, `ActualLength`, `TCDRLength`
    ORDER BY `ResponsibleOrg` ASC, `RecordCount` DESC, `ResponsibleOrgOut` ASC, `ActualLength` DESC, `TCDRLength` DESC
    LIMIT 0, 2000

编辑 1

使用@Barmar的建议，我的查询现在看起来像这样，导致错误:#1248 - 每个派生表必须有自己的别名，所以我显然实现了它错误，所以任何进一步的帮助将不胜感激。

    SELECT
        COUNT(*) AS `RecordCount`, `ExistingPlanned`, `ResponsibleOrg`, `StartDateTCDR`, `EndDateTCDR`, `SpecificEndDate`, `ResponsibleOrgOut`,
        (CASE
            WHEN (`EndDateTCDR` < '1001-01-01' AND `SpecificEndDate` = 0) THEN '2018-12-31'
            WHEN (`EndDateTCDR` < '1001-01-01' AND `SpecificEndDate` = 1) THEN '-'
            ELSE `EndDateTCDR`
        END) AS `EndDate`,
        (CASE
            WHEN (`StartDateTCDR` < '1001-01-01') THEN '-'
            ELSE `StartDateTCDR`
        END) AS `StartDate`,
        (CASE
            WHEN (`StartDateTCDR` < '1001-01-01') THEN '-'
            WHEN (`StartDateTCDR` > '1001-01-01' AND `EndDateTCDR` < '1001-01-01' AND `SpecificEndDate` = 1) THEN '-'
            WHEN (`StartDateTCDR` > '1001-01-01' AND `EndDateTCDR` < '1001-01-01' AND `SpecificEndDate` = 0) THEN (
                CASE
                    WHEN (YEAR('2018-12-31') - YEAR(`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') < DATE_FORMAT(`StartDateTCDR`, '%m%d')) >= 30) THEN '>= 30 (inclusive)'
                    WHEN (YEAR('2018-12-31') - YEAR(`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') < DATE_FORMAT(`StartDateTCDR`, '%m%d')) >= 20) THEN '>= 20 (inclusive)'
                    WHEN (YEAR('2018-12-31') - YEAR(`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') < DATE_FORMAT(`StartDateTCDR`, '%m%d')) >= 10) THEN '>= 10 (inclusive)'
                    ELSE '< 10'
                END
            )
            WHEN (`StartDateTCDR` > '1001-01-01' AND `EndDateTCDR` > '1001-01-01') THEN (
                CASE
                    WHEN (YEAR(`EndDateTCDR`) - YEAR(`StartDateTCDR`) - (DATE_FORMAT(`EndDateTCDR`, '%m%d') < DATE_FORMAT(`StartDateTCDR`, '%m%d')) >= 30) THEN '>= 30 (inclusive)'
                    WHEN (YEAR(`EndDateTCDR`) - YEAR(`StartDateTCDR`) - (DATE_FORMAT(`EndDateTCDR`, '%m%d') < DATE_FORMAT(`StartDateTCDR`, '%m%d')) >= 20) THEN '>= 20 (inclusive)'
                    WHEN (YEAR(`EndDateTCDR`) - YEAR(`StartDateTCDR`) - (DATE_FORMAT(`EndDateTCDR`, '%m%d') < DATE_FORMAT(`StartDateTCDR`, '%m%d')) >= 10) THEN '>= 10 (inclusive)'
                    ELSE '< 10'
                END
            )
        END) AS `TCDRLength`,
        (CASE
            WHEN (`StartDateTCDR` < '1001-01-01') THEN '-'
            WHEN (`StartDateTCDR` > '1001-01-01' AND `EndDateTCDR` < '1001-01-01' AND `SpecificEndDate` = 1) THEN '-'
            WHEN (`StartDateTCDR` > '1001-01-01' AND `EndDateTCDR` < '1001-01-01' AND `SpecificEndDate` = 0) THEN (
                ROUND((DATEDIFF('2018-12-31', `StartDateTCDR`) / 365), 2)
            )
            WHEN (`StartDateTCDR` > '1001-01-01' AND `EndDateTCDR` > '1001-01-01') THEN (
                ROUND((DATEDIFF(`EndDateTCDR`, `StartDateTCDR`) / 365), 2)
            )
        END) AS `ActualLength`
    FROM `data_records`
    LEFT JOIN `RespOrgLUT` ON `ResponsibleOrgIn` = `ResponsibleOrg`
    LEFT JOIN (
        SELECT `category`, COUNT(*)
        FROM `data_records` dr
        JOIN (
            SELECT '< 10' AS `category`
            UNION
            SELECT '>= 10'
            UNION
            SELECT '>= 20'
            UNION
            SELECT '>= 30'
        ) AS c
            ON (`category` = '>= 30' AND (YEAR('2018-12-31') - YEAR(`dr`.`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') < DATE_FORMAT(`dr`.`StartDateTCDR`, '%m%d')) >= 30))
            OR (`category` = '>= 20' AND (YEAR('2018-12-31') - YEAR(`dr`.`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') < DATE_FORMAT(`dr`.`StartDateTCDR`, '%m%d')) >= 20))
            OR (`category` = '>= 10' AND (YEAR('2018-12-31') - YEAR(`dr`.`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') < DATE_FORMAT(`dr`.`StartDateTCDR`, '%m%d')) >= 10))
            OR (`category` = '< 10' AND (YEAR('2018-12-31') - YEAR(`dr`.`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') < DATE_FORMAT(`dr`.`StartDateTCDR`, '%m%d')) < 10))
    )
    GROUP BY `ExistingPlanned`, `ResponsibleOrg`, `StartDateTCDR`, `EndDateTCDR`, `SpecificEndDate`, `ResponsibleOrgOut`, `StartDate`, `EndDate`, `ActualLength`, `TCDRLength`
    ORDER BY `ResponsibleOrg` ASC, `RecordCount` DESC, `ResponsibleOrgOut` ASC, `ActualLength` DESC, `TCDRLength` DESC
    LIMIT 0, 2000

Answer 1

使用返回所有类别的子查询连接表，然后检查该行是否适合该类别。以下是仅显示查询的该部分的简化内容，我现在没有时间填写您的整个查询。

SELECT category, COUNT(*)
FROM ...
JOIN (
    SELECT '< 10' AS category
    UNION
    SELECT '>= 10'
    UNION
    SELECT '>= 20'
    UNION
    SELECT '>= 30'
) AS c ON (category = '>= 30' AND (YEAR('2018-12-31') - YEAR(`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') < DATE_FORMAT(`StartDateTCDR`, '%m%d')) >= 30))
        OR (category = '>= 20' AND (YEAR('2018-12-31') - YEAR(`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') < DATE_FORMAT(`StartDateTCDR`, '%m%d')) >= 20))
        OR (category = '>= 10' AND (YEAR('2018-12-31') - YEAR(`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') < DATE_FORMAT(`StartDateTCDR`, '%m%d')) >= 10))
        OR (category = '< 10' AND (YEAR('2018-12-31') - YEAR(`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') < DATE_FORMAT(`StartDateTCDR`, '%m%d')) < 10))
GROUP BY Category, `ExistingPlanned`, `ResponsibleOrg`, `StartDateTCDR`, `EndDateTCDR`, `SpecificEndDate`, `ResponsibleOrgOut`, `StartDate`, `EndDate`, `ActualLength`, `TCDRLength`

另一种方法是使用 4 个查询的 UNION，每个查询都在其 WHERE 子句中测试年龄。