sql 问题

create table t_user (id number,name varchar2(10))
insert into t_user values (1,'Tom');
insert into t_user values (2,'Jerry');
insert into t_user values (3,'Jack');
create table t_papers( pid number,oid number,vid number,cid number);
insert into t_papers values (101,1,1,1);
insert into t_papers values (102,1,2,1);
with t_sum as(
select 'oid' as tp, oid ,count() cnt from t_papers group by oid
union all
select 'vid' as tp ,vid,count() from t_papers group by vid
union all
select 'cid' as tp ,cid,count() from t_papers group by cid
)
select name ,sum(onum),sum(vnum),sum(cnum) from (
select name, decode(tp,'oid' ,cnt,0) onum ,decode(tp,'vid' ,cnt,0) vnum ,decode(tp,'cid' ,cnt,0) cnum
from (
select aname,btp,boid,bcnt from t_user a left join
t_sum b on aid =boid)) group by name
其中 pid 表示文档id oid 表示文档创建者 vid表示文档审核者 cid表示文档发布者
结果如下
1 Jack 0 0 0
2 Jerry 0 1 0
3 Tom 2 1 2
看看是不是你想要的
还有一种方法
with t_s_q as
(select uuid, sum(oid) oid, sum(vid) vid, sum(cid) cid
from (select nvl(nvl(oid, vid), cid) uuid,
nvl2(oid, 1, 0) cnt oid,
nvl2(vid, 1, 0) cnt vid,
nvl2(cid, 1, 0) cnt cid,
cnt
from (select grouping(oid) od,
grouping(vid) vd,
grouping(cid) cd,
oid,
vid,
cid,
count() cnt
from t_papers
group by cube(oid, vid, cid))
where od + vd + cd = 2)
group by uuid)
select aid, aname, nvl(void, 0), nvl(vvid, 0), nvl(vcid, 0)
from t_user a
left join t_s_q v
on aid = vuuid

---