--oracle 分页
SELECT * FROM (SELECT A.*,ROWNUM RN FROM table A WHERE ROWNUM <= 15) WHERE RN > 1;
select t1.name,t1.sn from student t1 inner join sc t2 on t1.sn = t2.student_sn
inner join course t3 on t2.cource_no =t3.no where t3.cource_name='语文'
--查询张三选修的课程
select t1.cource_name from course t1 inner join sc t2 on t1.no =t2.cource_no
inner join student t3 on t2.student_sn=t3.sn where t3.name='张三'
--查询选修了2门课程的学生姓名和学号
select sn,name from student where sn IN
(select student_sn from sc group by student_sn having count(*)=2)
--自动递增
create sequence CX_PT_VIEW_S
minvalue 1
maxvalue 9999999999999999
start with 101
increment by 1 --每次递增
cache 20; --缓存
--select cx_pt_view_s.nextval from dual
create table EMP
(
ID VARCHAR2(64),
NAME VARCHAR2(16),
PID VARCHAR2(64)
)
-- 查询技术部及下属所有部门
select * from emp
start with name = '技术部'
connect by prior id = pid;
-- 查询技术部及上级所有部门
select * from emp
start with name = '技术部'
connect by prior pid = id;
-- 分段段统计人数
create table STAFF
(
ID VARCHAR2(64),
NAME VARCHAR2(16),
AGE NUMBER(3)
)
select case
when age between 20 and 29 then '20-29'
when age between 30 and 39 then '30-39'
when age>=40 or age<20 then '其他' end as 年龄段,
count(*) as 人数
from staff
group by case
when age between 20 and 29 then '20-29'
when age between 30 and 39 then '30-39'
when age>=40 or age<20 then '其他' end;