常见sql查询

来源：互联网发布：猪八戒的前世今生知乎编辑：程序博客网时间：2024/05/21 17:38

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;

0 0