这几个表不可能是实际数据库中的样子吧,建的不大好select t3.DEPARTMENT,
sum(1),
sum(case getValue(t1.userName) when 0 then 1 else 0 end),
sum(case getValue(t1.userName) when 1 then 1 else 0 end)
from op_log t1,op_access t2,dept_c t3
where t3.DEP_NO=t2.DEPARTMENT and t2.USERID=t1.USERID
group by t3.DEPARTMENT;getValue函数是将 移动方式 转为1,将 WEB方式 转为0
sum(1),
sum(case getValue(t1.userName) when 0 then 1 else 0 end),
sum(case getValue(t1.userName) when 1 then 1 else 0 end)
from op_log t1,op_access t2,dept_c t3
where t3.DEP_NO=t2.DEPARTMENT and t2.USERID=t1.USERID
group by t3.DEPARTMENT;getValue函数是将 移动方式 转为1,将 WEB方式 转为0
sum(decode(username,'web',1)) as Webfield
from op_log a ,op_access b
where a.userid=b.userid group by b.department
sum(decode(a.username,'web',1)) as Webfield
from op_log a ,op_access b
where a.userid=b.userid group by b.department
sum(decode(a.username,'web',1,0)) as Webfield
from op_log a ,op_access b
where a.userid=b.userid group by b.department
select c.department,count(a.userid),sum(decode(a.username,'移动用户',1,0)),sum(decode(a.username,移动用户'',0,1))
from op_log a,op_access b,dept_c c
where a.userid=b.userid and b.department=c.dept_no
group by c.department;感谢大家,结贴!