oracle常见sql笔试题,一路SQL笔试题

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