mysql 分段统计的两种方法

最新推荐文章于 2024-07-22 03:09:47 发布

Dylan Song

最新推荐文章于 2024-07-22 03:09:47 发布

阅读量1.7k

点赞数 1

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_44931584/article/details/109645772

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

一 sum + case 函数

 SELECT
		    sum(CASE when t.money <= 100000 then 1 else 0 end)   AS a   ,
        sum(CASE when  t.money > 100000 and   t.money <= 500000 then 1 else 0 end)  AS b    ,
        sum(CASE when  t.money > 500000 and   t.money <= 1000000 then 1 else 0 end)  AS c  ,
        sum(CASE when  t.money > 1000000 and t.money <= 5000000 then 1 else 0 end)  AS d    ,
        sum(CASE when  t.money > 5000000 and  t.money <= 10000000  then 1 else 0 end)   AS e ,
        sum(CASE when  t.money > 10000000 and  t.money <= 15000000  then 1 else 0 end)   AS f ,
        sum(CASE when  t.money > 15000000 and   t.money <= 20000000  then 1 else 0 end)   AS g 
        FROM
        (SELECT
        SUM(amount_money) AS money
        FROM
        `hx_winning_bid_info` where is_del = '0'
        GROUP BY tender_project_id) AS t

二 count 函数

  SELECT
        COUNT(t.money >= 0 AND t.money < 100000 OR NULL) AS zeroToOht,
        COUNT(t.money >= 100000 AND t.money < 500000 OR NULL) AS ohtToFht,
        COUNT(t.money >= 500000 AND t.money < 1000000 OR NULL) AS fhtToOm,
        COUNT(t.money >= 1000000 AND t.money < 5000000 OR NULL) AS omToFm,
        COUNT(t.money >= 5000000 AND t.money < 10000000 OR NULL) AS fmToTm,
        COUNT(t.money >= 10000000 AND t.money < 15000000 OR NULL) AS tmToFm,
        COUNT(t.money >= 15000000 AND t.money < 20000000 OR NULL) AS fmToTtm,
        COUNT(t.money >= 20000000 OR NULL) AS moreThanTwentyMillion
        FROM
        (SELECT
        SUM(amount_money) AS money
        FROM
        `hx_winning_bid_info` where is_del = '0'
        GROUP BY tender_project_id) AS t