我有一个从别名为“Length”的 CASE WHEN THEN 状态(或者可以使用多个 IF 语句)计算的 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 个月”,这是不正确的。
你需要使用整个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
clause.
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)