将不同项目分组到存储桶中的 sql 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6669730/
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
sql query that groups different items into buckets
提问by samuel_C
I am trying to write a query that returns the count of items whose price falls into certrain buckets:
我正在尝试编写一个查询，该查询返回价格落入 certrain 存储桶的商品数量：
For example if my table is:
例如，如果我的表是：
item_name | price
i1 | 2
i2 | 12
i3 | 4
i4 | 16
i5 | 6
output:
输出：
range | number of item
0 - 10 | 3
10 - 20 | 2
The way I am doing it so far is
到目前为止我这样做的方式是
SELECT count(*)
FROM my_table
Where price >=0
and price <10
then
然后
SELECT count(*)
FROM my_table
Where price >=10
and price <20
and then copy pasting my results each time into excel.
然后每次将我的结果复制粘贴到excel中。
Is there an automatic way to do this in an sql query?
在 sql 查询中是否有自动执行此操作的方法？
回答by DRapp
An expanded option from what Kerrek described, you can do you grouping based on a case/when
Kerrek 描述的扩展选项，您可以根据案例/时间进行分组
select
case when price >= 0 and price <= 10 then ' 0 - 10'
when price > 10 and price <= 50 then ' 10+ - 50'
when price > 50 and price <= 100 then ' 50+ - 100'
else 'over 100'
end PriceRange,
count(*) as TotalWithinRange
from
YourTable
group by 1
Here, the "group by 1" represents the ordinal column in your select statement... in this case, the case/when as TotalWithinRange.
在这里，“group by 1”代表选择语句中的序数列……在这种情况下，case/when 为 TotalWithinRange。
回答by Kerrek SB
You can try grouping by 10 units of price:
您可以尝试按 10 个价格单位分组：
SELECT COUNT(*) AS tally,
FLOOR(price/10) AS prange,
CONCAT(10*FLOOR(price/10), "-", 10*FLOOR(price/10)+9) AS rstr
FROM my_table
GROUP BY prange;
回答by JK0124
A bit of modification of DRapp's code...
对DRapp的代码稍作修改...
select
case when price >= 0 and price < 10 then " 0 - 10"
when price > 10 and price <= 50 then " 10+ - 50"
when price > 50 and price <= 100 then " 50+ - 100"
else "over 100"
end As PriceRange,
count(item_name) as ItemTotal
from YourTable
group by
case when price >= 0 and price < 10 then " 0 - 10"
when price > 10 and price <= 50 then " 10+ - 50"
when price > 50 and price <= 100 then " 50+ - 100"
else "over 100"
end;
