有一个表 TELL 四个字段 ID CALLER CALLEE DATE 数据如下:1 1001 0231001 2010-09-11
2 1001 0231001 2010-09-11
3 1001 0101001 2010-09-11
4 1001 0101001 2010-09-11
5 1001 0101001 2010-09-11
6 1001 04111001 2010-09-11
7 1001 04111001 2010-09-11
8 1001 04111001 2010-09-11
9 1001 04111001 2010-09-11callee 中的023,010,0411 分别代表区号现在要分组查询出重庆,北京,大连的次数。条件是 日期 ,想得到结果如下:重庆 2
北京 3
大连 4
2 1001 0231001 2010-09-11
3 1001 0101001 2010-09-11
4 1001 0101001 2010-09-11
5 1001 0101001 2010-09-11
6 1001 04111001 2010-09-11
7 1001 04111001 2010-09-11
8 1001 04111001 2010-09-11
9 1001 04111001 2010-09-11callee 中的023,010,0411 分别代表区号现在要分组查询出重庆,北京,大连的次数。条件是 日期 ,想得到结果如下:重庆 2
北京 3
大连 4
from TELL
where DATE=&输入日期
group by CALLER
按你的业务场景,应该有张表是存储区号的,两张表关联取。
用区号表的区号字段去和被叫关联 用 callee like 区号字段||'%'进行关联,以区号表的区号来分组统计。
你可以用取下callee字段的第二位做个decode然后进行分组。
当然有可能不会很准确,毕竟总是有特例的。
from (
select CALLEE,row_num() over (partition by CALLEE,order by DATE) rn
from TELL) a
group by CALLEE
字段分别是 ID cityName cityNO 如数据:
1 重庆 023
2 北京 010
3 大连0411
那结合之前的问题,又如何分组呢?
where to_char(DATE,'yyyy-mm-dd') ='2009-01-01'
group by substr(CALLER,0,3)
drop table tell;
drop table area_num;create table tell(
id number(18,0),
caller number(18,0),
callee varchar2(20),
cdate date -- date 是关键字,如果用关键字作为字段名,建表将出错:( ORA-00904: : 标识符无效 )
);alter table tell add constraints tell_pk primary key(id);create table area_num ( -- 区号表
areaid varchar2(20), -- 区号
areaname varchar2(20) -- 地区名称
);alter table area_num add constraints area_num_pk primary key(areaid);insert into tell(id,caller,callee,cdate) values(1, 1001, '0231001', to_date('2010-09-11','yyyy-mm-dd'));
insert into tell(id,caller,callee,cdate) values(2, 1001, '0231001', to_date('2010-09-11','yyyy-mm-dd'));
insert into tell(id,caller,callee,cdate) values(3, 1001, '0101001', to_date('2010-09-11','yyyy-mm-dd'));
insert into tell(id,caller,callee,cdate) values(4, 1001, '0101001', to_date('2010-09-11','yyyy-mm-dd'));
insert into tell(id,caller,callee,cdate) values(5, 1001, '0101001', to_date('2010-09-11','yyyy-mm-dd'));
insert into tell(id,caller,callee,cdate) values(6, 1001, '04111001', to_date('2010-09-11','yyyy-mm-dd'));
insert into tell(id,caller,callee,cdate) values(7, 1001, '04111001', to_date('2010-09-11','yyyy-mm-dd'));
insert into tell(id,caller,callee,cdate) values(8, 1001, '04111001', to_date('2010-09-11','yyyy-mm-dd'));
insert into tell(id,caller,callee,cdate) values(9, 1001, '04111001', to_date('2010-09-11','yyyy-mm-dd'));insert into area_num(areaid,areaname) values('023','重庆');
insert into area_num(areaid,areaname) values('010','北京');
insert into area_num(areaid,areaname) values('0411','大连');commit;select t1.areaid, t1.areaname, count(t2.id) as cnt
from area_num t1 inner join tell t2 on t1.areaid=substr(t2.callee,1,length(t1.areaid))
group by t1.areaid, t1.areaname
order by t1.areaid;-- or select t1.areaname, count(t2.id) as cnt
from area_num t1 inner join tell t2 on t1.areaid=substr(t2.callee,1,length(t1.areaid))
group by t1.areaid, t1.areaname
order by t1.areaid;