我现在有两个表,
表a记录借阅信息,分组统计如下:
select adminname,count(usernum) 借阅 from readtable group by adminnameadminname 借阅
李四 1
王五 4
张三 5表b记录打印信息,分组统计如下:
select adminname,count(usernum) 打印 from printable group by adminnameadminname 打印
李四 2
王五 3
张三 2我想问,怎么样对两表一起进行分组统计,获取如下结果:
adminname 借阅 打印
李四 1 2
王五 4 3
张三 5 2
在线等
表a记录借阅信息,分组统计如下:
select adminname,count(usernum) 借阅 from readtable group by adminnameadminname 借阅
李四 1
王五 4
张三 5表b记录打印信息,分组统计如下:
select adminname,count(usernum) 打印 from printable group by adminnameadminname 打印
李四 2
王五 3
张三 2我想问,怎么样对两表一起进行分组统计,获取如下结果:
adminname 借阅 打印
李四 1 2
王五 4 3
张三 5 2
在线等
nvl(m.借阅,0) 借阅,
nvl(n.打印,0) 打印
from
(select adminname,count(usernum) 借阅 from readtable group by adminname ) m
full join
(select adminname,count(usernum) 打印 from printable group by adminname ) n
on m.adminname = n.adminname
select R.adminname ,count(R.usernum) 借阅,count(P.usernum) 打印, from readtable R, printtable P where R.adminname = P.adminname
select adminname,count(usernum) 借阅,(select count(usernum) from biaob where biaob.adminname= biaoa.adminname ) 打印
from biaoa group by adminname;lz把表名换成自己的就行
可以修改一下
select decode(m.adminname,'',n.adminname,m.adminname) adminname,
nvl(m.借阅,0) 借阅,
nvl(n.打印,0) 打印
from
(select adminname,count(usernum) 借阅 from readtable group by adminname ) m
full join
(select adminname,count(usernum) 打印 from printable group by adminname ) n
on m.adminname = n.adminname
select '李四' name,1 借阅 from dual
union all
select '王五' name,4 借阅 from dual
union all
select '张三' name,5 借阅 from dual
union all
select '李六' name,2 借阅 from dual
),
b as(
select '李四' name,2 打印 from dual
union all
select '王五' name,3 打印 from dual
union all
select '张三' name,2 打印 from dual
union all
select '马八' name,1 打印 from dual
)
select nvl(name1,name2),借阅,打印 from(
select a.name name1,b.name name2,借阅,打印 from a FULL OUTER JOIN b
on (a.name = b.name)
)没有借阅和没有打印的都考虑到了