mysql when多个条件_MySQL-在CASE中组合多个WHEN条件

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;

解决方案

how about this?

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, ....

The problem is that you are grouping the records org.size causing <26 at two different groups since they are originally 0 and 1.

This will work,

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