SQL 查询我有一个表名 student 有两列 name 和 status

Question

我有一个名为 student 的表，其中包含两列 name 和 status

CREATE TABLE student
(
  name varchar(30) , status varchar(30) 
);

INSERT INTO student 
(name, status) 
VALUES 
('a','pass'),
('a','fail'),
('a','pass'),
('b','fail'),
('b','fail'),
('b','pass');

查询应该显示 3 列

name pass fail
a    2      1
b    1      2

即对应第 1 次计数的学生姓名。结果中应显示他们通过的科目数和未通过的科目数。

Answer 1

select name,
sum(case when status = 'pass' then 1 else 0 end) as sumPass,
sum(case when status = 'fail' then 1 else 0 end) as sumFail
From student
group by name

和 sqlFiddle