想要一个SQL按值的范围在oracle中分组值
问题描述:
我有一个表t1并且有一列名为days,所以我想将这些天分组1天到5天,6-15天和超过15天,然后计算每个组的计数,但是我不知道如何编写sql,谁能告诉我?结果应该看起来像下面:想要一个SQL按值的范围在oracle中分组值
Number Scope(days)
5 1-5
7 6-15
10 15+
答
如果我没有理解好,这可能是一个办法:
with t1(days) as (
select 1 from dual union all
select 2 from dual union all
select 5 from dual union all
select 6 from dual union all
select 7 from dual union all
select 8 from dual union all
select 10 from dual union all
select 11 from dual union all
select 16 from dual union all
select 17 from dual union all
select 19 from dual union all
select 19 from dual
)
/* the query */
select count(*),
case
when days between 1 and 5 then '1-5'
when days between 6 and 15 then '6-15'
else '+15'
end
from t1
group by case
when days between 1 and 5 then '1-5'
when days between 6 and 15 then '6-15'
else '+15'
end
给出:
COUNT(*) CASE
---------- ----
3 1-5
4 +15
5 6-15
的想法是被什么东西聚集那就是说每个数字所在的“组”,并且您可以使用CASE
轻松构建这样的信息。
Accordin到Jarlh的建议,这可以被改写为
select count(*), the_group
from (
select case
when days between 1 and 5 then '1-5'
when days between 6 and 15 then '6-15'
else '+15'
end the_group
from t1
)
group by the_group
显然,这假设你只有正数。
你有试过什么吗? – Aleksej
我完全不知道 – user2575502