create table student(cclass varchar2(10),cname varchar2(20),csex varchar2(10)); insert into student select '一班','王五','男' from dual union all select '二班','李子','男' from dual union all select '一班','张丝','女' from dual; / commit; --执行查询 select cclass,count(cname) num,sum(decode(csex,'男',1)) boy,sum(decode(csex,'女',1,0)) girl from student group by cclass order by cclass desc --执行结果 一班 2 1 1 二班 1 1 0
insert into student
select '一班','王五','男' from dual union all
select '二班','李子','男' from dual union all
select '一班','张丝','女' from dual;
/
commit;
--执行查询
select cclass,count(cname) num,sum(decode(csex,'男',1)) boy,sum(decode(csex,'女',1,0)) girl
from student
group by cclass
order by cclass desc
--执行结果
一班 2 1 1
二班 1 1 0