SQL codeSQL> with t(教师号,星期号,是否有课) as(
2 select 1,2,’有’ from dual
3 union all select 1,3,’有’ from dual
4 union all select 2,1,’有’ from dual
5 union all select 3,2,’有’ from dual
6 union all select 1,2,’有’ from dual
7 )
8 select 教师号,
9 sum(case when 星期号=1 and 是否有课=’有’ then 1 else 0 end) 星期一,
10 sum(case when 星期号=2 and 是否有课=’有’ then 1 else 0 end) 星期二,
11 sum(case when 星期号=3 and 是否有课=’有’ then 1 else 0 end) 星期三
12 from t
13 group by 教师号
14 order by 教师号;
教师号 星期一 星期二 星期三
———- ———- ———- ———-
1 0 2 1
2 1 0 0
3 0 1 0
——解决方案——————–SQL codeSQL> select * from teacher;
ID WEEK H
———- ———- –
1 2 y
1 3 y
2 1 y
3 2 y
1 2 y
SQL> select id,sum(case when week = 1 then 1 else null end) mon,sum(case when week = 2 then 1 else null end ) tue,sum(case when week = 3 then 1 else null end) wed from teacher group by id;
ID MON TUE WED
———- ———- ———- ———-
1 2 1
2 1
3 1
——解决方案——————–
CREATE TABLE t_teacher (t_id NUMBER,t_q NUMBER ,t_yn VARCHAR2(10));
INSERT INTO t_teacher VALUES(1,2,’有’);
INSERT INTO t_teacher VALUES(1,3,’有’);
INSERT INTO t_teacher VALUES(2,1,’有’);
INSERT INTO t_teacher VALUES(3,2,’有’);
INSERT INTO t_teacher VALUES(1,2,’有’);
COMMIT;
SELECT t.t_Id 教师号,
SUM(CASE
WHEN t.t_q = 1 AND t.t_Yn = ‘有’ THEN
1
ELSE
NULL
END) 星期一,
sum(CASE
WHEN t.t_q = 2 AND t.t_Yn = ‘有’ THEN
1
ELSE
NULL
END) 星期二,
sum(CASE
WHEN t.t_q = 3 AND t.t_Yn = ‘有’ THEN
1
ELSE
NULL
END) 星期三
FROM t_Teacher t
GROUP BY t.t_Id
——解决方案——————–
select teacherno 教师号,
sum(case when flag=’有’ and weekday=1 then 1 else 0 end) 星期一,
sum(case when flag=’有’ and weekday=2 then 1 else 0 end) 星期二,
sum(case when flag=’有’ and weekday=3 then 1 else 0 end) 星期三,
sum(case when flag=’有’ and weekday=4 then 1 else 0 end) 星期四,
sum(case when flag=’有’ and weekday=5 then 1 else 0 end) 星期五,
sum(case when flag=’有’ and weekday=6 or weekday=7 then 1 else 0 end) 星期日
from test
group by teacherno
发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/202147.html原文链接：https://javaforall.net
