如题:查出 每个人的通话记录在这个月(就暂定为2011年3月吧)被评分的次数、这个月的第一周通话被评分的次数,这个月的第二周通话被 评分的次数....各周?(这里的周的解释: 一个月的第一天如果为星期日,把这天单独立为一个星期算,所以一个月可能出现六个周的情况)
因为原表的字段、关系太多就简化成下面的样子,不知道有没有多去了东西。建表的语句-- 通话表
create table MYCALLIN
(
CALLID NUMBER,
CALLTIME DATE not null,
DEALUSER NUMBER not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column MYCALLIN.CALLID
is '通话id';
comment on column MYCALLIN.CALLTIME
is '通话时间';
comment on column MYCALLIN.DEALUSER
is '处理人';
-- Create table 评分表
create table MYSCORES
(
SCOREID NUMBER not null,
FORUSERID NUMBER,
CALLID NUMBER
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column MYSCORES.SCOREID
is '评分id';
comment on column MYSCORES.FORUSERID
is '被评分人的id';
comment on column MYSCORES.CALLID
is '通话id';测试数据:
insert into myscores (SCOREID, FORUSERID, CALLID)
values (130603384278701, 130319864686021, 130508178274076);insert into myscores (SCOREID, FORUSERID, CALLID)
values (130612927837521, 130319864686011, 130510477002504);insert into myscores (SCOREID, FORUSERID, CALLID)
values (130614114594001, 128488095280955, 129957636885670);insert into myscores (SCOREID, FORUSERID, CALLID)
values (130614115934003, 128488095280955, 129957587584165);insert into myscores (SCOREID, FORUSERID, CALLID)
values (130614119237805, 128488095280955, 129957481051563);insert into myscores (SCOREID, FORUSERID, CALLID)
values (130614120627207, 128488095280955, 129956942350650);insert into myscores (SCOREID, FORUSERID, CALLID)
values (130614123073909, 128488095280955, 129956807524846);insert into mycallin (CALLID, CALLTIME, DEALUSER)
values (129956807524846, to_date('08-03-2011 15:07:55', 'dd-mm-yyyy hh24:mi:ss'), 128488095280955);insert into mycallin (CALLID, CALLTIME, DEALUSER)
values (129956942350650, to_date('08-03-2011 15:30:23', 'dd-mm-yyyy hh24:mi:ss'), 128488095280955);insert into mycallin (CALLID, CALLTIME, DEALUSER)
values (129957481051563, to_date('08-03-2011 17:00:10', 'dd-mm-yyyy hh24:mi:ss'), 128488095280955);insert into mycallin (CALLID, CALLTIME, DEALUSER)
values (129957587584165, to_date('08-03-2011 17:17:55', 'dd-mm-yyyy hh24:mi:ss'), 128488095280955);insert into mycallin (CALLID, CALLTIME, DEALUSER)
values (129957636885670, to_date('08-03-2011 17:26:08', 'dd-mm-yyyy hh24:mi:ss'), 128488095280955);insert into mycallin (CALLID, CALLTIME, DEALUSER)
values (130508178274076, to_date('11-05-2011 10:43:02', 'dd-mm-yyyy hh24:mi:ss'), 130319864686021);insert into mycallin (CALLID, CALLTIME, DEALUSER)
values (130510477002504, to_date('11-05-2011 17:06:10', 'dd-mm-yyyy hh24:mi:ss'), 130319864686011);--这个是我查的每个人的总数,接下里就不知道 怎么查每个人每周的总数了,这里要一次连接着查询出来,请大家帮忙看看select count(score_id),for_user from scores b1 LEFT JOIN
(select ci.call_in_id from call_in ci where ci.call_in_time between '1-3月-2011' and '1-4月-2011') b2
ON b1.call_id=b2.call_in_id group by b1.for_user
因为原表的字段、关系太多就简化成下面的样子,不知道有没有多去了东西。建表的语句-- 通话表
create table MYCALLIN
(
CALLID NUMBER,
CALLTIME DATE not null,
DEALUSER NUMBER not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column MYCALLIN.CALLID
is '通话id';
comment on column MYCALLIN.CALLTIME
is '通话时间';
comment on column MYCALLIN.DEALUSER
is '处理人';
-- Create table 评分表
create table MYSCORES
(
SCOREID NUMBER not null,
FORUSERID NUMBER,
CALLID NUMBER
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column MYSCORES.SCOREID
is '评分id';
comment on column MYSCORES.FORUSERID
is '被评分人的id';
comment on column MYSCORES.CALLID
is '通话id';测试数据:
insert into myscores (SCOREID, FORUSERID, CALLID)
values (130603384278701, 130319864686021, 130508178274076);insert into myscores (SCOREID, FORUSERID, CALLID)
values (130612927837521, 130319864686011, 130510477002504);insert into myscores (SCOREID, FORUSERID, CALLID)
values (130614114594001, 128488095280955, 129957636885670);insert into myscores (SCOREID, FORUSERID, CALLID)
values (130614115934003, 128488095280955, 129957587584165);insert into myscores (SCOREID, FORUSERID, CALLID)
values (130614119237805, 128488095280955, 129957481051563);insert into myscores (SCOREID, FORUSERID, CALLID)
values (130614120627207, 128488095280955, 129956942350650);insert into myscores (SCOREID, FORUSERID, CALLID)
values (130614123073909, 128488095280955, 129956807524846);insert into mycallin (CALLID, CALLTIME, DEALUSER)
values (129956807524846, to_date('08-03-2011 15:07:55', 'dd-mm-yyyy hh24:mi:ss'), 128488095280955);insert into mycallin (CALLID, CALLTIME, DEALUSER)
values (129956942350650, to_date('08-03-2011 15:30:23', 'dd-mm-yyyy hh24:mi:ss'), 128488095280955);insert into mycallin (CALLID, CALLTIME, DEALUSER)
values (129957481051563, to_date('08-03-2011 17:00:10', 'dd-mm-yyyy hh24:mi:ss'), 128488095280955);insert into mycallin (CALLID, CALLTIME, DEALUSER)
values (129957587584165, to_date('08-03-2011 17:17:55', 'dd-mm-yyyy hh24:mi:ss'), 128488095280955);insert into mycallin (CALLID, CALLTIME, DEALUSER)
values (129957636885670, to_date('08-03-2011 17:26:08', 'dd-mm-yyyy hh24:mi:ss'), 128488095280955);insert into mycallin (CALLID, CALLTIME, DEALUSER)
values (130508178274076, to_date('11-05-2011 10:43:02', 'dd-mm-yyyy hh24:mi:ss'), 130319864686021);insert into mycallin (CALLID, CALLTIME, DEALUSER)
values (130510477002504, to_date('11-05-2011 17:06:10', 'dd-mm-yyyy hh24:mi:ss'), 130319864686011);--这个是我查的每个人的总数,接下里就不知道 怎么查每个人每周的总数了,这里要一次连接着查询出来,请大家帮忙看看select count(score_id),for_user from scores b1 LEFT JOIN
(select ci.call_in_id from call_in ci where ci.call_in_time between '1-3月-2011' and '1-4月-2011') b2
ON b1.call_id=b2.call_in_id group by b1.for_user
解决方案 »
- 高分求解联合查询语句!
- 在linux下,启动oracle,提示LRM-00109: could not open parameter file.. initorcl.ora文件
- 一句sql的性能优化问题!(能执行半个小时的一段sql)
- 再请教一句sql
- 今天考试几个不知道的问题!
- procedure 运行相当慢, 但是会有结果
- 在oracle中dbms_warning和plsql_warning
- 得到相同NO對應Q的最後一次變更的DATE ,謝謝
- 帮忙查看一下两个SQL语句执行效率的差异
- oracle11g 审计
- alter index PARALLEL 起什么作用的?
- Alter Index Allocate Extent 有什么用处?
FROM scores b1
LEFT JOIN (SELECT ci.call_in_id, ci.call_in_time
FROM call_in ci
WHERE ci.call_in_time BETWEEN '1-3月-2011' AND '1-4月-2011') b2
ON b1.call_id = b2.call_in_id
GROUP BY b1.for_user, to_char(b2.call_in_time, 'w');
我想要的是这样的结果月总量 第一周总量 第二周总量 第三周总量...
10 2 4 4...
COUNT(DECODE(to_char(b2.call_in_time, 'w'),'1',1)) week1,
COUNT(DECODE(to_char(b2.call_in_time, 'w'),'2',1)) week2,
COUNT(DECODE(to_char(b2.call_in_time, 'w'),'3',1)) week3,
COUNT(DECODE(to_char(b2.call_in_time, 'w'),'4',1)) week4,
COUNT(DECODE(to_char(b2.call_in_time, 'w'),'5',1)) week5,
COUNT(DECODE(to_char(b2.call_in_time, 'w'),'6',1)) week6
FROM scores b1
LEFT JOIN (SELECT ci.call_in_id, ci.call_in_time
FROM call_in ci
WHERE ci.call_in_time BETWEEN '1-3月-2011' AND '1-4月-2011') b2
ON b1.call_id = b2.call_in_id
GROUP BY b1.for_user;
values (129956807524846, to_date('01-03-2011 15:07:55', 'dd-mm-yyyy hh24:mi:ss'), 128488095280955);insert into mycallin (CALLID, CALLTIME, DEALUSER)
values (129956942350650, to_date('03-03-2011 15:30:23', 'dd-mm-yyyy hh24:mi:ss'), 128488095280955);insert into mycallin (CALLID, CALLTIME, DEALUSER)
values (129957636885670, to_date('15-03-2011 17:26:08', 'dd-mm-yyyy hh24:mi:ss'), 128488095280955);
--创建过程:
create or replace procedure calltelphone(
stime varchar2, --开始时间
etime varchar2, --结束时间
resultSet out sys_refcursor --返回结果集
)
is
cnt number:=0; --总周数
sql_str varchar2(4000); --动态sql语句
begin
cnt:= ceil(to_char(last_day(to_date(stime,'yyyy.mm.dd')),'dd')/7) ;
sql_str:='select a.foruserid,' ;
for i in 1..cnt loop
sql_str:=sql_str||'sum(decode(b.week,'||i||',1,0)) as '||'week_'||i||',' ;
end loop;
sql_str:=sql_str||'count(b.callid) as total ' ;
sql_str:=sql_str||'from myscores a left outer join
(select c.*,to_char(c.calltime,''w'') week
from mycallin c where calltime between '''||stime||''' and '''||etime||'''
) b
on a.callid=b.callid
group by a.foruserid order by a.foruserid' ;
open resultSet for sql_str;
end;
/
--结果:
FORUSERID WEEK_1 WEEK_2 WEEK_3 WEEK_4 WEEK_5 TOTAL
---------------------------------------------------------------------
128488095280955 2 5 1 0 0 8
130319864686011 0 0 0 0 0 0
130319864686021 0 0 0 0 0 0
还有 to_char(date,'w')这个得出来的是 这个日期 处于当前月的第几周,这个第几周好像是按 7天一周这样推算下去的吧, 我这里可能需要按这种情况规定周, 例如:2011/01/01 为星期六 那么 下一天 2011/01/02也为第一个星期,周一 2011/01/03 就要把它当成是第 二周了。