MySQL - 在 CASE 中组合多个 WHEN 条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18991091/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - Combining multiple WHEN conditions in CASE
提问by Ken
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.
我正在尝试使用以下 CASE 子句将 2 行结果合并为 1 行。'<26' 应该只出现一次，并且结果应该合并。
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;
回答by John Woo
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.sizecausing <26at two different groups since they are originally 0and 1.
问题是，你是分组记录org.size造成<26在两个不同的群体，因为他们原本0和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
