select 员工,成绩,decode(count(*),'null',0,count(*)),打分人 from table group by 员工 ,打分人 没测试过 你试试看
问题是我已经用过DECODE分组了
create table t1 (员工 varchar2(10), 成绩 varchar2(10), 打分人 varchar2(10)); insert into t1 values ('11','合格','111'); insert into t1 values ('22','合格','222'); insert into t1 values ('33','合格','111'); insert into t1 values ('33','合格','111'); insert into t1 values ('11','合格','222'); insert into t1 values ('11','合格','222'); insert into t1 values ('11','合格','333'); insert into t1 values ('22','合格','444'); insert into t1 values ('33','合格','555'); select 员工,成绩, (select count(1) from t1 a where a.员工=t1.员工 and a.打分人=t2.打分人) 合格数, t2.打分人 from t1,(select distinct 打分人 from t1) t2 group by 员工,成绩,t2.打分人 order by 员工 员工 成绩 合格数 打分人 ------------------------------------------------ 1 11 合格 1 111 2 11 合格 2 222 3 11 合格 1 333 4 11 合格 0 444 5 11 合格 0 555 6 22 合格 0 111 7 22 合格 1 222 8 22 合格 0 333 9 22 合格 1 444 10 22 合格 0 555 11 33 合格 2 111 12 33 合格 0 222 13 33 合格 0 333 14 33 合格 0 444 15 33 合格 1 555
with t as( select '11' yg,'合格' cj,'111' dfr from dual union all select '22','合格','222' from dual union all select '33','合格','111' from dual union all select '33','合格','111' from dual union all select '22','合格','333' from dual union all select '22','合格','444' from dual union all select '22','合格','555' from dual ), t_dfr as( select '111' dfr from dual union all select '222' from dual union all select '333' from dual union all select '444' from dual union all select '555' from dual ) select yg, t_dfr.dfr, sum(decode(t.dfr, t_dfr.dfr, 1, 0)) c from t, t_dfr group by yg, t_dfr.dfr order by yg, t_dfr.dfr YG DFR C -- --- ---------- 11 111 1 11 222 0 11 333 0 11 444 0 11 555 0 22 111 0 22 222 1 22 333 1 22 444 1 22 555 1 33 111 2 33 222 0 33 333 0 33 444 0 33 555 0
15 rows selected
with t as( select '11' yg,'合格' cj,'111' dfr from dual union all select '22','合格','222' from dual union all select '33','合格','111' from dual union all select '33','合格','111' from dual union all select '22','合格','333' from dual union all select '22','合格','444' from dual union all select '22','合格','555' from dual ), t_dfr as( select '111' dfr from dual union all select '222' from dual union all select '333' from dual union all select '444' from dual union all select '555' from dual ) select yg, t_dfr.dfr, sum(decode(t.dfr, t_dfr.dfr, 1, 0)) c from t, t_dfr group by yg, t_dfr.dfr order by yg, t_dfr.dfr YG DFR C -- --- ---------- 11 111 1 11 222 0 11 333 0 11 444 0 11 555 0 22 111 0 22 222 1 22 333 1 22 444 1 22 555 1 33 111 2 33 222 0 33 333 0 33 444 0 33 555 0
15 rows selected
方法同上,都是需要 两表关联,将 打分人 作为维表使用。还有一种做法,是直接从 源表 过滤 出 2 张表,再进行关联查询。select from (group by 员工) a (distinct 员工) b
SELECT a.员工,a.成绩,decode(a.打分人,b.打分人,COUNT(*),0),b.打分人 FROM table a,table b GROUP BY a.员工,a.成绩,b.打分人,a.打分人 ORDER BY a.员工;注:我已经验证通过,希望能帮助你
修改一下: SELECT a.员工,a.成绩,decode(a.员工,b.员工, decode(a.打分人,b.打分人,COUNT(*),0),0),b.打分人 FROM table a,table b GROUP BY a.员工,b.员工,a.成绩,b.打分人,a.打分人 ORDER BY a.员工;
修改下: SELECT a.员工,a.成绩,decode(a.打分人,b.打分人,COUNT(*),0),b.打分人 FROM table a, (select distinct table.打分人 from table) b GROUP BY a.员工,a.成绩,b.打分人,a.打分人 ORDER BY a.员工;
没测试过 你试试看
create table t1 (员工 varchar2(10), 成绩 varchar2(10), 打分人 varchar2(10));
insert into t1 values ('11','合格','111');
insert into t1 values ('22','合格','222');
insert into t1 values ('33','合格','111');
insert into t1 values ('33','合格','111');
insert into t1 values ('11','合格','222');
insert into t1 values ('11','合格','222');
insert into t1 values ('11','合格','333');
insert into t1 values ('22','合格','444');
insert into t1 values ('33','合格','555');
select 员工,成绩,
(select count(1) from t1 a where a.员工=t1.员工 and a.打分人=t2.打分人) 合格数,
t2.打分人
from t1,(select distinct 打分人 from t1) t2
group by 员工,成绩,t2.打分人
order by 员工 员工 成绩 合格数 打分人
------------------------------------------------
1 11 合格 1 111
2 11 合格 2 222
3 11 合格 1 333
4 11 合格 0 444
5 11 合格 0 555
6 22 合格 0 111
7 22 合格 1 222
8 22 合格 0 333
9 22 合格 1 444
10 22 合格 0 555
11 33 合格 2 111
12 33 合格 0 222
13 33 合格 0 333
14 33 合格 0 444
15 33 合格 1 555
with t as(
select '11' yg,'合格' cj,'111' dfr from dual
union all
select '22','合格','222' from dual
union all
select '33','合格','111' from dual
union all
select '33','合格','111' from dual
union all
select '22','合格','333' from dual
union all
select '22','合格','444' from dual
union all
select '22','合格','555' from dual
), t_dfr as(
select '111' dfr from dual
union all
select '222' from dual
union all
select '333' from dual
union all
select '444' from dual
union all
select '555' from dual
)
select yg, t_dfr.dfr, sum(decode(t.dfr, t_dfr.dfr, 1, 0)) c
from t, t_dfr
group by yg, t_dfr.dfr
order by yg, t_dfr.dfr
YG DFR C
-- --- ----------
11 111 1
11 222 0
11 333 0
11 444 0
11 555 0
22 111 0
22 222 1
22 333 1
22 444 1
22 555 1
33 111 2
33 222 0
33 333 0
33 444 0
33 555 0
15 rows selected
with t as(
select '11' yg,'合格' cj,'111' dfr from dual
union all
select '22','合格','222' from dual
union all
select '33','合格','111' from dual
union all
select '33','合格','111' from dual
union all
select '22','合格','333' from dual
union all
select '22','合格','444' from dual
union all
select '22','合格','555' from dual
), t_dfr as(
select '111' dfr from dual
union all
select '222' from dual
union all
select '333' from dual
union all
select '444' from dual
union all
select '555' from dual
)
select yg, t_dfr.dfr, sum(decode(t.dfr, t_dfr.dfr, 1, 0)) c
from t, t_dfr
group by yg, t_dfr.dfr
order by yg, t_dfr.dfr
YG DFR C
-- --- ----------
11 111 1
11 222 0
11 333 0
11 444 0
11 555 0
22 111 0
22 222 1
22 333 1
22 444 1
22 555 1
33 111 2
33 222 0
33 333 0
33 444 0
33 555 0
15 rows selected
(group by 员工) a
(distinct 员工) b
SELECT a.员工,a.成绩,decode(a.员工,b.员工,
decode(a.打分人,b.打分人,COUNT(*),0),0),b.打分人 FROM table a,table b GROUP BY a.员工,b.员工,a.成绩,b.打分人,a.打分人 ORDER BY a.员工;
SELECT a.员工,a.成绩,decode(a.打分人,b.打分人,COUNT(*),0),b.打分人 FROM table a,
(select distinct table.打分人 from table) b GROUP BY a.员工,a.成绩,b.打分人,a.打分人 ORDER BY a.员工;