SQL

最新推荐文章于 2024-07-24 12:06:09 发布

hh_yy_2014

最新推荐文章于 2024-07-24 12:06:09 发布

阅读量101

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/ggq0000000008/article/details/107227290

版权

SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1 查询相差小时数

/* oracle 计算相差小时数*/
select ceil((to_date('2019-05-03 10:00:00', 'yyyy-mm-dd hh24-mi-ss') - to_date('2019-05-03 10:00:00', 'yyyy-mm-dd hh24-mi-ss')) * 24) 相差小时数 from dual;

2 ORACLE 偏移分析函数 lag()与lead()

/*ORACLE 偏移分析函数 lag()与lead() */
SELECT G.*, lag(工资,1,'example') OVER(order by 部门) 偏移后工资
    from ( select '张三' 人员,'财务' 部门, '5000' 工资  from dual
          union 
          select '李四','财务', '5200'  from dual
          union 
          select '王五','人事', '4000' from dual
          union 
          select '刘二','研发', '6500' from dual
          union 
          select '陈一','研发', '6500' from dual
          union 
          select '赵六','研发', '7000' from dual) G

3 Oracle聚合函数 rank()

/* oracle聚合函数 rank*/
with t1 as
          (select '张三' 人员, '财务' 部门, '5000' 工资
             from dual
           union
           select '李四', '财务', '5200'
             from dual
           union
           select '王五', '人事', '4000'
             from dual
           union
           select '刘二', '研发', '6500'
             from dual
           union
           select '陈一', '研发', '6600'
             from dual
           union
           select '赵六', '研发', '7000' from dual)
         
select * from (
    SELECT t1.*, rank() OVER(PARTITION BY 部门 ORDER BY 工资) rank FROM t1 
) where rank=1

4 oracle decode

/* oracle decode */
with t2 as(
select 'zhangsan' ename, 15000 sal
          from dual
        union
        select 'lisi', 9000
          from dual
        union
        select 'wangwu', 4000 from dual
)


SELECT t2.*,
       DECODE(SIGN(t2.sal - 10000),
              1,
              '高薪',
              0,
              '高薪',
              -1,
              DECODE(SIGN(t2.sal - 5000), 1, '中等', 0, '中等', -1, '低薪')) gongzi
from t2;