有一个学生记录的表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 notnull,

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 1else 0 end ) as '男' ,女=sum(case sex when '女' then 1 else 0end)

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 Mfrom student where sex='男'group  by  class

union

select   class,0 F ,count(*) Mfrom 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