/*
create table tb_student(
idd number(4),
namee varchar2(20),
course varchar2(20),
score number(5,2)
);
insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
commit;
select * from tb_student;
drop table tb_student;
*/
--使用一条SQL语句,查询出每门课都大于80分的学生姓名
select namee from tb_student group by namee having min(score)>80 and count(1)=(select count(distinct course) from tb_student)
--行转列
select namee,min(decode(course,'语文',score) )语文,min(decode(course,'数学',score)) 数学,min(decode(course,'英语',score)) 英语 from tb_student group by namee
---表连接
--92语法:
--select 数据from 数据源1,数据源2...;--无连接条件,笛卡尔积结果。
--select 数据from 数据源1,数据源2...where 行过滤条件 ; 等值连接
--select * from emp,dept where emp.deptno=dept.deptno;
--先连接后过滤
--select * from emp,dept where emp.deptno=dept.deptno and emp.deptno=30;
--先过滤后连接[相对效率高,因为连接的数据较少]
select * from emp where deptno=30;
select * from dept where deptno=30;
select * from (select * from emp where deptno=30),(select * from dept where deptno=30);
--select 数据from 数据源1,数据源2...where 行过滤条件 ; 非等值连接
--select * from emp,salgrade where emp.sal between 1200 and 1400;
ORACLE
最新推荐文章于 2024-10-16 10:24:17 发布