您可以在CASE WHEN THEN别名下进行GROUP BY吗？

一尘不染

您可以在CASE WHEN THEN别名下进行GROUP BY吗？

mysql

我有一个从CASE WHEN THEN状态（或可以使用多个IF语句）别名为“
Length”的情况下计算出的SELECT语句，我需要正确地将结果分组在一起。SELECT似乎有效，但是该组将它们分组错误。这是我的声明：

SELECT CASE 
    WHEN DATEDIFF(o.EndDate, o.StartDate) < 30 THEN '<1 Month'
    WHEN DATEDIFF(o.EndDate, o.StartDate) < 90 THEN '1 - 2 Months'
    WHEN DATEDIFF(o.EndDate, o.StartDate) < 210 THEN '3 - 4 Months'
    ELSE '>4 Months' END AS 'Length', 
    COUNT(DISTINCT(person.ID)) AS 'COUNT'
FROM person
    INNER JOIN opportunity AS o
    INNER JOIN Organization AS org
    ON person.EntityID = o.id 
        AND O.OrganizationID = Org.ID
WHERE person.TitleID = 2
    AND o.bID = 1
GROUP BY 'Length'
ORDER BY 'Length' ASC;

这会将所有结果分组为“ 3-4个月”，这是不正确的。

阅读 1141

2020-05-17

共1个答案

一尘不染

如果您没有将整个CASE语句GROUP BY包装在子查询中，则需要使用该语句中的整个语句。

SELECT  CASE 
            WHEN DATEDIFF(o.EndDate, o.StartDate) < 30 THEN '<1 Month'
            WHEN DATEDIFF(o.EndDate, o.StartDate) < 90 THEN '1 - 2 Months'
            WHEN DATEDIFF(o.EndDate, o.StartDate) < 210 THEN '3 - 4 Months'
            ELSE '>4 Months' 
        END AS `Length`, 
        COUNT(DISTINCT(person.ID)) AS `COUNT`
FROM    person
        INNER JOIN opportunity AS o
            ON person.EntityID = o.id
        INNER JOIN Organization AS org
            ON o.OrganizationID = Org.ID
WHERE   person.TitleID = 2
        AND o.bID = 1
GROUP   BY  CASE 
                WHEN DATEDIFF(o.EndDate, o.StartDate) < 30 THEN '<1 Month'
                WHEN DATEDIFF(o.EndDate, o.StartDate) < 90 THEN '1 - 2 Months'
                WHEN DATEDIFF(o.EndDate, o.StartDate) < 210 THEN '3 - 4 Months'
                ELSE '>4 Months' 
            END
ORDER   BY Length ASC;

还要删除ORDER BY子句中列名周围的单引号。

2020-05-17