常见sql查询

--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;