MySQL 和具有一系列值的 CASE WHEN

发布于 2024-09-03 05:07:16 字数 727 浏览 3 评论 0原文

我有一个帐户表和一个记录表，其中帐户有多个记录。我想按“记录数”范围细分帐户总数。的细分

Count of Records | Count
=========================
0-25 | 100
25 - 50 | 122
50 - 100 | 300

即显示等

。我正在使用以下查询，但无法将其按我想要的“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

原文

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?

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

唔猫 2024-09-10 05:07:16

尝试这个：

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 子句中定义的一个或多个表达式是这些存储桶的“定义”。

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

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.

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

回复收藏 0 原文

征棹 2024-09-10 05:07:16

您需要一个子查询。如果这是一个视图，那么您需要使用两个视图。

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;

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;

回复收藏 0 原文

~没有更多了~