每日一练（三）一个union all的例子，三个题-CSDN博客

一：
表结构是这样的
SNAME CNAME
------------------------- -------------------------
张三数学
张三物理
张三语文
张三化学
李四数学
李四语文
李四化学
王五数学
王五物理
王五语文
赵四数学
赵四物理
赵四语文
周七数学
周七物理
15 rows selected

求：
1 ：只选数学，物理，语文的学生, 写出相应 SQL 语句
2 ：同时选了数学，物理，语文的学生, 查询结果如下, 写出相应 SQL
1：
select sname, cname
from (select sname, cname, count(*) over(partition by sname) cnt
from course
where sname not in (select sname
from (select *
from course
left outer join (select '数学' cname1
from dual
union all
select '物理' cname1
from dual
union all
select '语文' cname1
from dual) c
on (course.cname = c.cname1))
where cname1 is null))
where cnt = 3;

SNAME CNAME
------------------------- -------------------------
王五数学
王五物理
王五语文
赵四数学
赵四语文
赵四物理
6 rows selected
2：
select sname, cname
from course
where sname in
(select sname
from (select sname, cname, count(*) over(partition by sname) cnt
from course
where cname in ('数学', '物理', '语文'))
where cnt >= 3);

SNAME CNAME
------------------------- -------------------------
张三数学
张三物理
张三语文
张三化学
王五数学
王五物理
王五语文
赵四数学
赵四物理
赵四语文
10 rows selected

二、
drop table students;
create table students(name varchar2(25),class varchar2(25),grade int);
insert into students values ('张三','语文',20);
insert into students values ('张三','数学',90);
insert into students values ('张三','英语',50);
insert into students values ('李四','语文',81);
insert into students values ('李四','数学',60);
insert into students values ('李四','英语',90);
insert into students values ('王二','数学',81);
insert into students values ('王二','英语',90);
insert into students values ('李五','数学',83);
insert into students values ('李五','英语',90);
insert into students values ('李五','化学',90);
commit;
有表 students(name,class,grade), 请用标准 sql 语句完成
name class grade
张三数学 81
李四语文 70
王五数学 90
张三语文 60
李四数学 100
王五语文 90
王五英语 81
要求: 用 sql 语句输出各门功课都大于 80 分的同学姓名
(1)
select *
from students
where name in
(select a.name
from (select name, count(*) over(partition by name) cnt
from students
where grade > 80) a
inner join (select name, count(*) over(partition by name) cnt1
from students) b
on (a.name = b.name)
where cnt = cnt1);

(2)超级简单的Group By

select name from students group by name having min(grade) >= 80;

NAME CLASS GRADE
------------------------- ------------------ ------------------------------
王二数学 81
王二英语 90
李五数学 83
李五英语 90
李五化学

三、
drop table students;
create table students(name varchar2(25),class varchar2(25),grade int);
insert into students values ('张三','语文',20);
insert into students values ('张三','数学',90);
insert into students values ('张三','英语',50);
insert into students values ('李四','语文',81);
insert into students values ('李四','数学',60);
insert into students values ('李四','英语',90);
commit;

已知一个表的结构为：
姓名科目成绩
张三语文 20
张三数学 30
张三英语 50
李四语文 70
李四数学 60
李四英语 90
过怎样通过 select 语句把他变成以下结构：
姓名语文数学英语
张三 20 30 50
李四 70 60 90

select name,
nvl(sum(case
when class = '语文' then
grade
else
null
end),
0) "语文",
nvl(sum(case
when class = '数学' then
grade
else
null
end),
0) "数学",
nvl(sum(case
when class = '英语' then
grade
else
0
end),
0) "英语"
from students
group by name;

NAME 语文数学英语
------------------------- ---------- ---------- ----------
王二 0 81 90
李四 81 60 90
李五 0 83 90
张三 20 90 50

select name,
nvl(max(decode(class, '语文', grade)),0) "语文",
nvl(max(decode(class, '数学', grade)),0) "数学",
nvl(max(decode(class, '英语', grade)),0) "英语"
from students
group by name;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31402276/viewspace-2127249/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31402276/viewspace-2127249/