您可以使用CASE WHEN别名来分组吗？ [英] Can you GROUP BY with a CASE WHEN THEN alias name?

问题描述

我有一个SELECT语句是从CASE WHEN THEN状态（或可以使用多个IF语句）中计算出来的，它被定义为'Length'，并且我需要将结果正确地分组在一起。 SELECT似乎正在工作，但该组将它们分组错了。这是我的声明：

pre $ SELECT CASE
WHEN DATEDIFF（o.EndDate，o.StartDate）< 30 THEN'<1个月'
当DATEDIFF（o.EndDate，o.StartDate）< 90 THEN'1 - 2 Months'
WHEN DATEDIFF（o.EndDate，o.StartDate）< 210 THEN'3 - 4个月'
ELSE'> 4个月'END as'长度'，
COUNT（DISTINCT（person.ID））由于'COUNT'
FROM person
INNER JOIN机会AS o
INNER JOIN组织AS org
ON person.EntityID = o.id
AND O.OrganizationID = Org.ID
WHERE person.TitleID = 2
和o.bID = 1
GROUP BY'长度'
ORDER BY'长度'ASC;


将所有结果归为3 - 4个月，这是不正确的。

解决方案

您需要在 CASE > GROUP BY 子句，如果您没有将其包装在子查询中。

pre code> SELECT CASE
WHEN DATEDIFF（o.EndDate，o.StartDate）< 30 THEN'<1个月'
当DATEDIFF（o.EndDate，o.StartDate）< 90 THEN'1 - 2 Months'
WHEN DATEDIFF（o.EndDate，o.StartDate）< 210 THEN'3 - 4个月'
ELSE'> 4个月'
END as`长度'，
COUNT（DISTINCT（person.ID））作为`COUNT`
FROM person
INNER JOIN机会AS o
ON person.EntityID = o.id
INNER JOIN组织AS org
ON o.OrganizationID = Org.ID
Where person .TitleID = 2
和o.bID = 1
GROUP BY CASE
WHEN DATEDIFF（o.EndDate，o.StartDate）< 30 THEN'<1个月'
当DATEDIFF（o.EndDate，o.StartDate）< 90 THEN'1 - 2 Months'
WHEN DATEDIFF（o.EndDate，o.StartDate）< 210 THEN'3 - 4个月'
ELSE'> 4个月'
END
ORDER BY长度ASC;

删除 ORDER BY 中列名的单引号， code>子句。

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;

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

解决方案

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;

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

这篇关于您可以使用CASE WHEN别名来分组吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！