create table jiaoshi(
教师号 varchar2(10),
星期号 varchar2(10),
是否有课 varchar2(10)
);
insert into jiaoshi values('1','2','有');
insert into jiaoshi values('1','3','有');
insert into jiaoshi values('2','1','有');
insert into jiaoshi values('3','2','有');
insert into jiaoshi values('1','2','有');
SELECT 教师号,SUM(星期一) AS 星期一,SUM(星期二) AS 星期二,SUM(星期三) AS 星期三,SUM(星期四) AS 星期四,SUM(星期五) AS 星期五
FROM(SELECT 教师号,
CASE WHEN 星期号=1 THEN 1 ELSE 0 END AS 星期一,
CASE WHEN 星期号=2 THEN 1 ELSE 0 END AS 星期二,
CASE WHEN 星期号=3 THEN 1 ELSE 0 END AS 星期三,
CASE WHEN 星期号=4 THEN 1 ELSE 0 END AS 星期四,
CASE WHEN 星期号=5 THEN 1 ELSE 0 END AS 星期五
FROM jiaoshi
) A GROUP BY 教师号