需求:
+-------------+---------------+-----------------+----------------+---------------------+------------------+
| student.id | student.name | student.gender | student.birth | student.department | student.address |
+-------------+---------------+-----------------+----------------+---------------------+------------------+
| 201901 | 张大佬 | 男 | 1985 | 计算机系 | 北京市海淀区 |
| 201902 | 郭大侠 | 男 | 1986 | 中文系 | 北京市昌平区 |
| 201903 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 |
| 201904 | 李四 | 男 | 1990 | 英语系 | 辽宁市阜新市 |
| 201905 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 |
| 201906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |
+-------------+---------------+-----------------+----------------+---------------------+------------------+
请写一条sql将以上数据, 转换成下面的结果
+-------+----+----+-----+
| 院系 | 男 | 女 | 总计 |
+-------+----+----+-----+
| 中文系 | 1 | 1 | 2 |
| 英语系 | 1 | 1 | 2 |
| 计算机系 | 2 | 0 | 2 |
+-------+----+----+-----+
– 建表
create table if not exists test.tb_student (
id string,
name string,
gender string,
birth string,
department string,
address string
)
row format delimited fields terminated by ",";
–导入数据
vi /doit/student
201901,张大佬,男,1985,计算机系,北京市海淀区
201902,郭大侠,男,1986,中文系,北京市昌平区
201903,张三,女,1990,中文系,湖南省永州市
201904,李四,男,1990,英语系,辽宁市阜新市
201905,王五,女,1991,英语系,福建省厦门市
201906,王六,男,1988,计算机系,湖南省衡阳市
load data local inpath "/doit/student" into table test.tb_student;
– 方法1
select
`院系`,
sum(`男`) `男`,
sum(`女`) `女`,
sum(`男`) + sum(`女`) as `总计`
from
(
select department `院系`,
case gender when "男" then 1 else 0 end `男`,
case gender when "女" then 1 else 0 end `女`
from tb_student
) a
group by `院系`;
– 方法1加强
select
department `院系`,
sum(case gender when "男" then 1 else 0 end) `男`,
sum(case gender when "女" then 1 else 0 end) `女`,
sum(case when gender = "女" or gender = "男" then 1 else 0 end) `总计`
from
tb_student
group by department
– 方法2
with tmp as (
select
department,
gender,
count (1) cnt
from tb_student
group by department,gender)
select
department `院系`,
nvl(gc['男'],0) `男`,
nvl(gc['女'],0) `女`,
-- floor是向下取整
floor(nvl(gc['男'],0) + nvl(gc['女'],0)) `总计`
from
(
select
department,
str_to_map(concat_ws(",",collect_list(concat_ws(":",gender,cast (cnt as string)))),",",":") gc
from
tmp
group by
department
) t