17,086
社区成员
发帖
与我相关
我的任务
分享
with t as(
select 10 deptid,'1' empid,0 sex from dual
union all
select 10,'2',1 from dual
union all
select 20,'3',0 from dual
union all
select 10,'4',0 from dual
union all
select 30,'5',1 from dual
union all
select 10,'6',0 from dual
union all
select 40,'7',1 from dual
union all
select 40,'8',1 from dual
union all
select 30,'9',0 from dual
)
select deptid "部门",
count(1) 部门人数,
sum(decode(sex, 0, 1, 0)) "部门男性人数",
sum(decode(sex, 0, 0, 1)) "部门女性人数",
sum(count(1)) over() "总数"
from t
group by deptid
部门 部门人数 部门男性人数 部门女性人数 总数
---------- ---------- ------------ ------------ ----------
10 4 3 1 9
20 1 1 0 9
30 2 1 1 9
40 2 0 2 9
create table b(
id varchar2(10) references a(id),--与a表id号类型相同
sums number(10),
fm number(10),
mm number(10)
)
/
create or replace procedure pro_show_emp(
id_in in a.id%type
)
as
begin
insert into b
select id,
count(*),
sum(decode(xb,'男',1,0)),
sum(decode(xb,'女',1,0))
from a
where id=id_in;
commit;
exception
when others then
rollback;
end pro_show_emp;