有一个
学生
记录的表student,形式如下:
create table student
(
)
go
insert into student values('admin',1,'男')
insert into student values('111',1,'女')
insert into student values('张三',2,'男')
insert into student values('hui',2,'女')
insert into student values('李四',2,'男')
insert into student values('xin',3,'女')
insert into student values('xin',3,'女')
go
select * from student
select class , sum(case sex when '男' then 1else 0 end ) as '男' ,女=sum(case sex when '女' then 1 else 0end)
from student group by class
ORACLE:
create table student
(
)
insert into student values(seq_id.nextval,'admin',1,'男');
insert into student values(seq_id.nextval,'111',1,'女');
insert into student values(seq_id.nextval,'张三',2,'男');
insert into student values(seq_id.nextval,'hui',2,'女');
insert into student values(seq_id.nextval,'李四',2,'男');
insert into student values(seq_id.nextval,'xin',3,'女');
insert into student values(seq_id.nextval,'xin',3,'女');
select * from student;
select class,sum(decode(sex,'男',1,0))男,sum(decode(sex,'女',1,0))女
from student
group by class;
select class,sum(F),sum(M)from
(
select
union
select
)
group
扩展:一个班级只有一个男生很女生,在一条记录上分别显示姓名
insert into student values('admin',1,'男')
insert into student values('111',1,'女')
insert into student values('张三',2,'男')
insert into student values('hui',2,'女')
预期结果
class
1
2
SQL:
①:
select a.class,a.sname as '男',b.sname as'女'
from student a inner join student b on a.class=b.class
where a.sex='男' and b.sex='女'
②:
select a.class,a.sname as '男' ,b.sname as'女' from
(select * from student where sex='男')a,
(select * from student where sex='女')b
where a.class=b.class