MySQL和CASE WHEN，值范围 [英] MySQL and CASE WHEN with a range of values

问题描述

我有一个帐户表和一个记录表，其中帐户有多个记录.我想按记录数"范围细分帐户总数. IE.显示细分

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

等等.

我正在使用以下查询，但是我无法通过"grp"将其分组，这是我想要的，有关修改查询的最佳方法的任何帮助吗?

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?

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

推荐答案

尝试一下:

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(*)

您必须定义"您想要将原始数据行聚合到的存储桶".这就是Group By子句的作用...它定义了基础表中每一行所依据的条件进行分析以确定将其数据聚合到哪个桶"中.group by子句中定义的一个或多个表达式是这些桶的定义".

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.

当查询处理原始数据行时，该表达式的值与现有存储桶相同的任何行都将聚合到该存储桶中...没有值的新行现有存储桶导致创建新存储桶...

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

这篇关于MySQL和CASE WHEN，值范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！