select 班级,sum(decode(性别,'男',1,0)) 男,sum(decode(性别,'女',1,0)) 女 from 表名 group by 班级;
SQL> create table student( 2 stuno int, 3 stuname varchar2(20), 4 sex varchar2(2), 5 class varchar2(20));表已创建。SQL> insert into student values(1,'tom','m','a');已创建 1 行。SQL> insert into student values(1,'lucy','f','a');已创建 1 行。SQL> insert into student values(3,'lily','f','a');已创建 1 行。SQL> insert into student values(4,'','m','b');已创建 1 行。SQL> insert into student values(5,'aspen','f','b');已创建 1 行。SQL> select * from student; STUNO STUNAME SE CLASS ---------- -------------------- -- -------------------- 1 tom m a 1 lucy f a 3 lily f a 4 m b 5 aspen f bSQL> select class,sum(decode(sex,'m',1,0)) M,count(*)-sum(decode(sex,'m',1,0)) F 2 from student group by class;CLASS M F -------------------- ---------- ---------- a 1 2 b 1 1
select 班级,sum(decode(性别,'男',1,0)) 男,sum(decode(性别,'女',1,0)) 女 from 表名 group by 班级;
SQL> create table student(
2 stuno int,
3 stuname varchar2(20),
4 sex varchar2(2),
5 class varchar2(20));表已创建。SQL> insert into student values(1,'tom','m','a');已创建 1 行。SQL> insert into student values(1,'lucy','f','a');已创建 1 行。SQL> insert into student values(3,'lily','f','a');已创建 1 行。SQL> insert into student values(4,'','m','b');已创建 1 行。SQL> insert into student values(5,'aspen','f','b');已创建 1 行。SQL> select * from student; STUNO STUNAME SE CLASS
---------- -------------------- -- --------------------
1 tom m a
1 lucy f a
3 lily f a
4 m b
5 aspen f bSQL> select class,sum(decode(sex,'m',1,0)) M,count(*)-sum(decode(sex,'m',1,0)) F
2 from student group by class;CLASS M F
-------------------- ---------- ----------
a 1 2
b 1 1