case when

建表插入数据

create table score(
 stu_code varchar(8) not null,
 stu_name varchar(8) not null,
 stu_sex int not null,
 stu_score int not null)
 ;
insert into score values('xm','小明',0,88);
insert into score values('xl','小磊',0,55);
insert into score values('xf','小峰',0,45);
insert into score values('xh','小红',0,66);
insert into score values('xn','晓妮',0,77);
insert into score values('xy','小伊',0,99);

SELECT 
SUM (CASE WHEN STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_COUNT,  --男生数量统计
SUM (CASE WHEN STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_COUNT,  --女生数量统计
SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_PASS,  --男生通过人数统计
SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_PASS  --女生数量统计
FROM score

postgres=# select (case when stu_sex =0 then 1 else 0 end) from thtf_students;

case

1
1
1
1
1
1

(6 rows)

postgres=# select case when stu_sex =0 then 1 else 0 end from thtf_students;

case

1
1
1
1
1
1

(6 rows)