一:
表结构是这样的
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/