求助：Oracle的一个分段SQL

[mofei168]

我有一个表，有一个字段记录每天访问人数的情况，我想以10人间隔为1段，统计下总数
如table1
-personcount-- date-
11 2011-1-1
22 2011-1-3
2 2011-1-4
5 2011-1-5
.... ....

要求统计
分段 该段总人数 天数
0-10 7 2
11-20 11 1
20-30 22 1

tanks

[zujinsheng]

是我理解能力太差么...10人为1段,少于10人可以理解..为什么有超过10人的呢..

[quanhj]

with t as ( 
select 11 as personcount,to_date('2011-01-01','yyyy-mm-dd') As datetime from dual 
union all 
select 22 as personcount,to_date('2011-01-03','yyyy-mm-dd') As datetime from dual 
union all 
select 2 as personcount,to_date('2011-01-04','yyyy-mm-dd') As datetime from dual 
union all 
select 5 as personcount,to_date('2011-01-05','yyyy-mm-dd') As datetime from dual 
)
select case when personcount >=0 And personcount <10 then '0-10' 
            when personcount >=10 And personcount <20 then '10-20' 
            when personcount >=20 And personcount <30  then '20-30' end as 分段,
       sum(personcount) 总人数,count(distinct datetime) as 总天数 
from t 
group by   case when personcount >=0 And personcount <10 then '0-10' 
            when personcount >=10 And personcount <20 then '10-20' 
            when personcount >=20 And personcount <30  then '20-30' end       

[cutebear2008]

显然不可能只有3段的啊，哥们！

估计需要像分页那样动态显示!
[Quote=引用 2 楼 quanhj 的回复:]
SQL code

with t as (
select 11 as personcount,to_date('2011-01-01','yyyy-mm-dd') As datetime from dual
union all
select 22 as personcount,to_date('2011-01-03','yyyy-mm-dd') As datetime from d……
[/Quote]

[oO寒枫Oo]

select (personcount-1)/10 as fenduan,
sum(personcount) as sum_per,
count(distinct riqi) as tianshu
from tb_name
group by (personcount-1)/10

[mofei168]

是的，有很多段，且不是固定的
[Quote=引用 3 楼 cutebear2008 的回复:]

显然不可能只有3段的啊，哥们！

估计需要像分页那样动态显示!
引用 2 楼 quanhj 的回复:
SQL code

with t as (
select 11 as personcount,to_date('2011-01-01','yyyy-mm-dd') As datetime from dual
union all
select 22 as personcount,……
[/Quote]

[quanhj]

[Quote=引用 4 楼 lxpbs8851 的回复:]
SQL code


select (personcount-1)/10 as fenduan,
sum(personcount) as sum_per,
count(distinct riqi) as tianshu
from tb_name
group by (personcount-1)/10
[/Quote]


with t as (
select 11 as personcount,to_date('2011-01-01','yyyy-mm-dd') As datetime from dual
union all
select 22 as personcount,to_date('2011-01-03','yyyy-mm-dd') As datetime from dual
union all
select 2 as personcount,to_date('2011-01-04','yyyy-mm-dd') As datetime from dual
union all
select 5 as personcount,to_date('2011-01-05','yyyy-mm-dd') As datetime from dual
)
select trunc( (personcount-1)/10) as 分段,
sum(personcount) as sum_per,
count(distinct datetime) as tianshu
from t
group by trunc((personcount-1)/10)

[qinglang1987]

有点复杂啊

[mofei168]

谢谢，存在personcount=0呢
[Quote=引用 4 楼 lxpbs8851 的回复:]

SQL code

select (personcount-1)/10 as fenduan,
sum(personcount) as sum_per,
count(distinct riqi) as tianshu
from tb_name
group by (personcount-1)/10
[/Quote]

[oO寒枫Oo]

select to_char(trunc((111-1)/10))||'0-'||to_char(trunc((111-1)/10)+1)||'0' as fenduan,
sum(personcount) as sum_per,
count(distinct riqi) as tianshu
from tb_name
group by to_char(trunc((111-1)/10))||'0-'||to_char(trunc((111-1)/10)+1)||'0'

[oO寒枫Oo]

[Quote=引用 8 楼 mofei168 的回复:]

谢谢，存在personcount=0呢
引用 4 楼 lxpbs8851 的回复:

SQL code

select (personcount-1)/10 as fenduan,
sum(personcount) as sum_per,
count(distinct riqi) as tianshu
from tb_name
group by (personcount-1)/……
[/Quote]
加个绝对值函数

select to_char(trunc(abs(personcount-1)/10))||'0-'||to_char(trunc(abs(personcount-1)/10)+1)||'0' as fenduan,
sum(personcount) as sum_per,
count(distinct riqi) as tianshu
from tb_name
group by to_char(trunc(abs(personcount-1)/10))||'0-'||to_char(trunc(abs(personcount-1)/10)+1)||'0'

[mofei168]

完美答案，非常感谢lxpbs8851

同时也感谢quanhj

[Quote=引用 10 楼 lxpbs8851 的回复:]

引用 8 楼 mofei168 的回复:

谢谢，存在personcount=0呢
引用 4 楼 lxpbs8851 的回复:

SQL code

select (personcount-1)/10 as fenduan,
sum(personcount) as sum_per,
count(distinct riqi) as tianshu
from tb_name
g……
[/Quote]