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

小编典典

如果您没有将整个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