题目：

    

1.建表语句

create table student (

     id varchar(20),

     name varchar(20),

     gender char(1),

     birth varchar(20),

     department varchar(20),

     address varchar(20)

) charset = utf8;

2.插入数据

insert into student values

("201901","张大佬","男","1985","计算机系","北京市海淀区"),

("201902","郭大侠","男","1986","中文系","北京市昌平区"),

("201903","张三","女","1990","中文系","湖南省永州市"),

("201904","李四","男","1990","英语系","辽宁市阜新市"),

("201905","王五","女","1991","英语系","福建省厦门市"),

("201906","王六","男","1988","计算机系","湖南省衡阳市");

结果如下

 

3.解题思路

select 

    department 院系,

    case gender when "男" then 1 else 0 end 男,

    case gender when "女" then 1 else 0 end 女

from student;

结果如下

 

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 student

) a

group by 院系;

结果如下