mysql group by case when 分月_mysql – 你可以把GROUP BY用一个CASE WHEN这个别名吗？

如果您不将其包装在子查询中,则需要在GROUP BY子句中使用整个CASE语句.

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