unicom_desk 台席表 id name 名字 hall_id 外键,大厅 user_id 外键,用户
unicom_hall 大厅表 id name
unicom_login_history 登陆历史表 id user_id login_time 登陆时间 logout_time 下线时间
unicom_work 工作时间表 id user_id desk_id start_time end_time work_time
unicom_user 用户表 id name 需求: c.name 台席,d.name 大厅,count(b.desk_id)台席办理业务次数,count(a.user_id) 登陆次数, count(b.desk_id)/count(a.user_id) 台席利用率 每周,每月,每年
是这样吗 select a.name 台席,b.name 大厅, count(d.desk_id)台席办理业务次数, (select count(1) from unicom_login_history where trunc(login_time,'iw')=trunc(d.start_time,'iw')) 登录次数, count(d.desk_id)/(select count(1) from unicom_login_history where trunc(login_time,'iw')=trunc(d.start_time,'iw')) 台席利用率 from unicom_desk a,unicom_hall b,,unicom_work d where a.hall_id=b.id and a.id=d.desk_id group by a.name,b.name,trunc(d.start_time,'iw')trunc里的iw改成yyyy是年,改成mm是月
wildwave,你来了啊,利用率是工作时间内办理业务次数/登陆次数。 d.start_time 报这个不在,group by 里面?
这样? select a.name 台席,b.name 大厅, count(d.desk_id)台席办理业务次数, (select count(1) from unicom_login_history where login_time>=to_date('2009-10-15','yyyy-mm-dd') and logout_time<to_date('2009-10-22','yyyy-mm-dd')) 登录次数, count(d.desk_id)/(select count(1) from unicom_login_history where login_time>=to_date('2009-10-15','yyyy-mm-dd') and logout_time<to_date('2009-10-22','yyyy-mm-dd')) 台席利用率 from unicom_desk a,unicom_hall b,unicom_work d where a.hall_id=b.id and a.id=d.desk_id and d.start_time>=to_date('2009-10-15','yyyy-mm-dd') and d.start_time<to_date('2009-10-22','yyyy-mm-dd') group by a.name,b.name
id
name 名字
hall_id 外键,大厅
user_id 外键,用户
unicom_hall 大厅表
id
name
unicom_login_history 登陆历史表
id
user_id
login_time 登陆时间
logout_time 下线时间
unicom_work 工作时间表
id
user_id
desk_id
start_time
end_time
work_time
unicom_user 用户表
id
name
需求:
c.name 台席,d.name 大厅,count(b.desk_id)台席办理业务次数,count(a.user_id) 登陆次数, count(b.desk_id)/count(a.user_id) 台席利用率
每周,每月,每年
select a.name 台席,b.name 大厅,
count(d.desk_id)台席办理业务次数,
(select count(1) from unicom_login_history where trunc(login_time,'iw')=trunc(d.start_time,'iw')) 登录次数,
count(d.desk_id)/(select count(1) from unicom_login_history where trunc(login_time,'iw')=trunc(d.start_time,'iw')) 台席利用率
from
unicom_desk a,unicom_hall b,,unicom_work d
where a.hall_id=b.id
and a.id=d.desk_id
group by a.name,b.name,trunc(d.start_time,'iw')trunc里的iw改成yyyy是年,改成mm是月
d.start_time 报这个不在,group by 里面?
上面的代码在from后面多了一个逗号。。还是有别的错误?
还是我没理解你的意图..如果有图会好理解一些
select a.name 台席,b.name 大厅,
count(d.desk_id)台席办理业务次数,
(select count(1) from unicom_login_history where login_time>=to_date('2009-10-15','yyyy-mm-dd')
and logout_time<to_date('2009-10-22','yyyy-mm-dd')) 登录次数,
count(d.desk_id)/(select count(1) from unicom_login_history where login_time>=to_date('2009-10-15','yyyy-mm-dd')
and logout_time<to_date('2009-10-22','yyyy-mm-dd')) 台席利用率
from
unicom_desk a,unicom_hall b,unicom_work d
where a.hall_id=b.id
and a.id=d.desk_id
and d.start_time>=to_date('2009-10-15','yyyy-mm-dd') and d.start_time<to_date('2009-10-22','yyyy-mm-dd')
group by a.name,b.name