您可以使用 CASE WHEN THEN 别名进行 GROUP BY 吗?

2021-12-27 00:00:00 sql group-by mysql case

我有一个 SELECT 语句是根据 CASE WHEN THEN 状态(或可以使用多个 IF 语句)计算的，别名为长度"，我需要正确地将结果分组在一起.SELECT 似乎正在工作，但该组将它们分组错误.这是我的声明:

I have a SELECT statement being calculated from a CASE WHEN THEN state (or could use multiple IF statements) aliased as 'Length', and I need to correctly GROUP the results together. The SELECT seems to be working, but the group groups them wrong. Here is my statement:

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 个月"，这是不正确的..

This groups all results into '3 - 4 Months' which isn't right..

推荐答案

GROUP BY 子句中的 CASE 语句如果不换行，则需要使用整个语句它在子查询中.

You need to use the whole CASE statement in the GROUP BY clause if you don't wrapped it in a subquery.

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 子句中列名周围的单引号.

Remove also the single quotes around the column name in the ORDER BY clause.

相关文章