MySQL查询将数据分组到不同的范围 [英] MySQL query to group data into different ranges

问题描述

我在这里找到了解决方案，将不同项目分组到桶中的 sql 查询

I found a solution for this here, sql query that groups different items into buckets

只要有所有范围的数据，它就可以很好地工作.如果没有数据，我希望查询返回 0.

It works well as long as there is data for all the ranges. I want the query to return 0 if there is no data.

所以如果我的桌子是:

item_name | price
i1        | 2
i2        | 22
i3        | 4
i4        | 26
i5        | 44
i6        | 6

我希望输出为:

range   | number of item
0 - 10  |  3
11 - 20 |  0
21 - 30 |  2
31 - 40 |  0
41 - 50 |  1

以下查询不会显示 0 计数情况的结果.

The below query will not display result for the 0 count cases.

select
      case when price >= 0 and price <= 10    then "  0 - 10"
           when price > 10 and price <= 20   then " 10 - 20"
           when price > 20 and price <= 30   then " 20 - 30"
           when price > 30 and price <= 40  then " 30 - 40"
           when price > 40 and price <= 50  then " 40 - 50"
           else "over 50"
      end PriceRange,
      count(*) as TotalWithinRange
   from
      YourTable
   group by 1

有没有人有解决方案?

推荐答案

您需要构建一个包含所有价格范围的内联表.然后根据您的查询对派生表执行 LEFT JOIN 以获得预期结果:

You need to build an inline table containing all price ranges. Then perform a LEFT JOIN with a derived table based on your query to get the expected result:

SELECT x.PriceRange, COALESCE(TotalWithinRange, 0) AS TotalWithinRange
FROM (
  SELECT "0 - 10" AS PriceRange 
  UNION SELECT "10 - 20"
  UNION SELECT "20 - 30"
  UNION SELECT "30 - 40"
  UNION SELECT "40 - 50"
  UNION SELECT "over 50" ) x
LEFT JOIN (  
   SELECT
      CASE when price >= 0 and price <= 10 then "0 - 10"
           when price > 10 and price <= 20 then "10 - 20"
           when price > 20 and price <= 30 then "20 - 30"
           when price > 30 and price <= 40 then "30 - 40"
           when price > 40 and price <= 50 then "40 - 50"
           else "over 50"
      END AS PriceRange,
      COUNT(*) as TotalWithinRange
   FROM YourTable
   GROUP BY 1 ) y ON x.PriceRange = y.PriceRange

SQL Fiddle 演示

这篇关于MySQL查询将数据分组到不同的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！