sql学习笔记

最近看到一些比较特殊的业务逻辑，用sql实现，顺便收集整理一下

 

1、oracle中递归查询
任务：查询某一经理下的所有员工信息，用start with ... connect by ......实现。

示例：

create table RC(eid int , mid int)--eid:empoyee ID , mid:manager ID

insert into rc
select 2,1 from dual
union
select 3,1 from dual
union
select 4,1 from dual
union
select 5,2 from dual
union
select 6,3 from dual
union
select 7,5 from dual

select eid,mid from rc start with mid=1 connect by prior eid=mid

 

2、实现排名，但不要用rank函数

通过rownum实现，分组后取得最小rownum后作为此组的名次。

select t1.sales,t2.rank_num
from employee t1,
        (select sales,min(rownum) as rank_num from
               (select sales from employee order by sales desc)
        group by sales) t2 where t1.sales=t2.sales(+) order by t1.sales desc

create table EMPSALES
(
  ID       NUMBER,
  NAME     VARCHAR2(20),
  IN_DATE  DATE,
  OUT_DATE DATE
)

 

3、上班时间计算：

用自连接实现。
select min(name) , min(tt.traffictime), tt.in_date from (
select name ,
floor((t.in_date-t.out_date)*60*24) as traffictime
,t.in_date ,t.out_date
from (
select t1.id,t1.name,t1.in_date,t2.out_date from empsales t1,(
select name, out_date from empsales)t2
where t1.name=t2.name and t1.out_date!=t2.out_date order by t1.in_date) t
) tt where tt.traffictime>0 group by tt.in_date

table定义：
create table EMPLOYEE
(
  ID    NUMBER,
  SALES NUMBER
)

 

4、带with check option选项的视图

语法：create or replace view V_Student as
select  id , name   from Student t where t.id=11 or t.id=10  with check option；

with check option选项的视图，在进行更新，添加操作时必须能在操作后的视图中包含相应修改的记录；如果是删除记录则必须是在现有视图中的记录，否则都会出现check不符合错误。