MySQL 和 CASE WHEN 具有一系列值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2913177/
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 and CASE WHEN with a range of values
提问by kickdaddy
I have an accounts table and a records table where accounts have multiple records. I would like to break down the account totals by "count of records" range. I.e. Show the breakdown of
我有一个帐户表和一个记录表，其中帐户有多个记录。我想按“记录数”范围细分帐户总数。即显示细分
Count of Records | Count
=========================
0-25 | 100
25 - 50 | 122
50 - 100 | 300
Etc.
等等。
I am using the following query, but I can't get it to group by "grp" which is what I want, any help on the best way to modify query?
我正在使用以下查询，但我无法将其按“grp”分组，这正是我想要的，有关修改查询的最佳方法有什么帮助吗？
SELECT count(*) as ct,
CASE
WHEN COUNT(*) < 25 THEN '1-25'
WHEN COUNT(*) >= 25 < 50 THEN '25-50'
WHEN COUNT(*) >= 50 < 100 THEN '50-100'
WHEN COUNT(*) >= 100 < 250 THEN '100-250'
WHEN COUNT(*) >= 250 < 500 THEN '250-500'
WHEN COUNT(*) >= 500 < 1000 THEN '500-1000'
ELSE '1000+'
END AS grp
FROM records r,accounts a
WHERE r.account_id=a.id
ORDER BY ct
回答by Charles Bretana
try this:
尝试这个：
SELECT count(*) as ct,
CASE
WHEN COUNT(*) < 25 THEN '1-25'
WHEN COUNT(*) >= 25 < 50 THEN '25-50'
WHEN COUNT(*) >= 50 < 100 THEN '50-100'
WHEN COUNT(*) >= 100 < 250 THEN '100-250'
WHEN COUNT(*) >= 250 < 500 THEN '250-500'
WHEN COUNT(*) >= 500 < 1000 THEN '500-1000'
ELSE '1000+'
END AS grp
FROM records r, accounts a
WHERE r.account_id=a.id
GROUP BY r.account_id, a.id,
CASE
WHEN COUNT(*) < 25 THEN '1-25'
WHEN COUNT(*) >= 25 < 50 THEN '25-50'
WHEN COUNT(*) >= 50 < 100 THEN '50-100'
WHEN COUNT(*) >= 100 < 250 THEN '100-250'
WHEN COUNT(*) >= 250 < 500 THEN '250-500'
WHEN COUNT(*) >= 500 < 1000 THEN '500-1000'
ELSE '1000+' END
ORDER BY count(*)
You have to "define" the "buckets" you wish to aggregate the original data rows into... This is what the Group By clause is for... It defines the criteria by which each row in the base tables will be analyzed to determine which "bucket" it's data will be aggregated into... The expression or expressions defined in the group by clause are the "definitions" for those buckets.
您必须“定义”您希望将原始数据行聚合到的“桶”……这就是 Group By 子句的用途……它定义了分析基表中每一行的标准确定它的数据将聚合到哪个“桶”中... group by 子句中定义的一个或多个表达式是这些桶的“定义”。
As the query processes the original data rows, any row for which the value(s) of this expression(s) are the same as an existing bucket is aggregated into that bucket... Any new row with a value not represented by an existing bucket causes a new bucket to be created...
当查询处理原始数据行时，此表达式的值与现有存储桶相同的任何行都将聚合到该存储桶中...任何具有未由现有存储桶表示的值的新行存储桶会导致创建一个新的存储桶...
回答by Sajjad Tariq
You need a sub-query. If this is a view you need to use two views then.
您需要一个子查询。如果这是一个视图，那么您需要使用两个视图。
SELECT s.ct, s.grp FROM (
SELECT count(*) as ct,
CASE
WHEN COUNT(*) < 25 THEN '1-25'
WHEN COUNT(*) >= 25 AND COUNT(*) < 50 THEN '25-50'
WHEN COUNT(*) >= 50 AND COUNT(*) < 100 THEN '50-100'
WHEN COUNT(*) >= 100 AND COUNT(*) < 250 THEN '100-250'
WHEN COUNT(*) >= 250 AND COUNT(*) < 500 THEN '250-500'
WHEN COUNT(*) >= 500 AND COUNT(*) < 1000 THEN '500-1000'
ELSE '1000+'
END AS grp
FROM records r,accounts a
WHERE r.account_id=a.id) as s
Group BY s.grp;
