MySQL-在CASE中组合多个WHEN条件 [英] MySQL - Combining multiple WHEN conditions in CASE
本文介绍了MySQL-在CASE中组合多个WHEN条件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试使用下面的CASE子句将2行结果合并为1行. < 26"仅应出现一次,并且结果应合并在一起.
I’m trying to combine 2 rows of results into 1 using the below CASE clause. ‘<26’ should only appear once, and the results should be combined.
SELECT
CASE org.size
WHEN 0 THEN '<26'
WHEN 1 THEN '<26'
WHEN 2 THEN '26-50'
WHEN 3 THEN '51-100'
WHEN 4 THEN '101-250'
WHEN 5 THEN '251-500'
WHEN 6 THEN '501-1000'
WHEN 7 THEN '1001-5000'
ELSE '5000+' END
AS 'Size',
COUNT(DISTINCT org.id) AS '# of Companies'
FROM org INNER JOIN usr ON usr.orgid = org.id
INNER JOIN usr_role ON usr.id = usr_role.usrid
WHERE org.deleted = 0 AND usr.brnd = 1 AND usr_role.role = 1
GROUP BY org.size;
推荐答案
如何?
SELECT CASE
WHEN org.size IN (0, 1) THEN '<26'
WHEN org.size = 2 THEN '26-50'
WHEN org.size = 3 THEN '51-100'
WHEN org.size = 4 THEN '101-250'
WHEN org.size = 5 THEN '251-500'
WHEN org.size = 6 THEN '501-1000'
WHEN org.size = 7 THEN '1001-5000'
ELSE '5000+' END AS Size, ....
问题是您将记录org.size
分组,导致<26
分为两个不同的组,因为它们最初是0
和1
.
The problem is that you are grouping the records org.size
causing <26
at two different groups since they are originally 0
and 1
.
这可以工作,
GROUP BY CASE
WHEN org.size IN (0, 1) THEN '<26'
WHEN org.size = 2 THEN '26-50'
WHEN org.size = 3 THEN '51-100'
WHEN org.size = 4 THEN '101-250'
WHEN org.size = 5 THEN '251-500'
WHEN org.size = 6 THEN '501-1000'
WHEN org.size = 7 THEN '1001-5000'
ELSE '5000+' END
这篇关于MySQL-在CASE中组合多个WHEN条件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文