有一个学生记录的表student，形式如下：    
  name   class    sex    
  1       a       男    
  2       b       女   
  3       c       男   
  4       a       男   
  5       a       女   
  6       a       男    
  ……    
  现要统计每个班级的男女人数，结果如下：    
  class   男  女    
  a       3   1    
  b       0   1    
  c       1   0    
  用一句select语句实现

 SQL:

create table student
(
  sID int identity(1,1) primary key not null,
  sname varchar(8) not null,
  class int not null,
  sex char(2) not null
)
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 1 else 0 end ) as '男' ,女=sum(case sex when '女' then 1 else 0 end)
from student group by class

ORACLE:

create table student
(
  sID number primary key not null,
  sname varchar2(8) not null,
  class int not null,
  sex char(2) not null
)

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   class,count(*) F, 0 M from student where sex='男' group   by   class  
union  
select   class,0 F ,count(*) M from student where sex='女' group   by   class  
)  
group   by   class

扩展:一个班级只有一个男生很女生,在一条记录上分别显示姓名

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           admin    111
2           张三     hui

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