Oracle统计男⼥学⽣总⼈数
SQL语句，统计出班级的男⼥⼈数有⼀个学⽣记录的表student，形式如下：
现要统计每个班级的男⼥⼈数，结果如下：
⽤⼀句select语句实现
S QL:
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
正確寫法如下：
(1) select class , sum(case sex when'男'then1else0end ) as'男' , sum(case sex when'⼥'then1else0end) as'⼥' from student
group by class；
或者：select class,sum(case when sex='男'then1end )as'男', sum(case when sex='⼥'then1end )as'⼥' 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(val,'admin',1,'男'); insert into student values(val,'111',1,'⼥'); insert into student values(val,'张三',2,'男'); insert into student values(val,'hui',2,'⼥'); insert into student values(val,'李四',2,'男'); insert into student values(val,'xin',3,'⼥'); insert into student values(val,'xin',3,'⼥');
select * from student;
正確寫法如下：
(1) select class,
sql统计每个系的学生人数sum(decode(sex,'男',1,0)) as '男',
sum(decode(sex,'⼥',1,0)) as '⼥'
from student
group by class;
(2) 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,'⼥')
Student表：
预期结果
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
（2）⽅法⼆：
统计某个班男⼥⽣⼈数:
1有如下表格和数据:
现要求通过SELECT语句查询出如下结果：
20u se studb
21g o
22c reate t able T estTable
24I D i nt identity(1,1) primary k ey,
25[Name]v archar(10),
26[Class]v archar(10),
27S ex c har(2)
28)
30i nsert T estTable v alues('张1','⼀班','男')
31i nsert T estTable v alues('张2','⼀班','男')
32i nsert T estTable v alues('张3','⼆班','⼥')
33i nsert T estTable v alues('张4','⼀班','男')
34i nsert T estTable v alues('张5','⼆班','⼥')
35i nsert T estTable v alues('张6','⼆班','⼥')
36i nsert T estTable v alues('张7','⼆班','男')
37i nsert T estTable v alues('张8','⼀班','⼥')
select*f rom T estTable
答案1(⽤sum()函數和case when )
(1)select class 班级, s um(case when S ex= '男'then 1end) a s'男',
s um(case when S ex='⼥'then1end) a s'⼥'
f rom TestTable group by class
(2)select class 班级, s um(case when S ex= '男'then 1 else 0 end) a s'男', s um(case when S ex='⼥'then1else 0 end) a s'⼥'
f rom TestTable group by class
(3)select class 班级, s um(case Sex when '男'then 1 else 0 end) a s'男', s um(case Sex when '⼥'then1else 0 end) a s'⼥'
f rom TestTable group by class
答案2：⽤sum()和decode()函數
(1)select class 班級,sum(decode(sex,’男’,1,0))as ‘男’,
Sum(decode(sex,’⼥’,1,0)) as ‘⼥’
From TestTable group by class;
答案3：
SELECT班级, SUM(男) AS男, SUM(⼥) AS⼥
FROM (
SELECT Class AS班级, COUNT(Sex) AS男, 0AS⼥FROM TestTable
WHERE Sex='男'
GROUP BY Class
UNION
SELECT Class AS班级, 0AS男, COUNT(Sex) AS⼥FROM TestTable
WHERE Sex='⼥'
GROUP BY Class
) A
GROUP BY班级
⼀．統計各個事業男⼥⼈數。
(1)⽤SUM()函數和decode()函數時寫法
(1)select bg_code,sum(decode(sex,'1',1,0)) as'男',
sum(decode(sex,'0',1,0)) as '⼥'
from chr_employee
group by bg_code；
(2)(I)⽤sum()函數和case when 的寫法：
(1)select bg_code,sum(case when sex='1'then1else0end ) as M, sum(case when sex='0'then1else0end ) as W
from chr_employee
group by bg_code;
(2)select bg_code,sum(case when sex='1'then1end ) as M, sum(case when sex='0'then1end ) as W
from chr_employee
group by bg_code;
(II)case when 的第⼆種⽤法。或者以下寫法：
(1)select bg_code,sum(case sex when'1'then1else0end ) as'男' , sum(case sex when'0'then1else0end) as'⼥'
from chr_employee
group by bg_code；
(2)select bg_code,sum(case sex when'1'then1end ) as'男' , sum(case sex when'0'then1end) as'⼥'
from chr_employee
group by bg_code；
錯誤寫法⽤count()函數(不能⽤count()函數)
(2) select bg_code,count(decode(sex,'1',1,0)) as'男',
count(decode(sex,'0',1,0)) as'⼥'
from chr_employee
group by bg_code;
(3)第三種正確寫法如下：
(3)select BU ,sum(B) as M,sum(G) as W from
(select bg_code as BU ,count(sex) as B,0as G
from testgroupby
where sex='1'
group by bg_code

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。