oracle笔记整理7——with as、case when、merge into、分析函数over()

这篇博客详细介绍了Oracle数据库中的几个高级操作:使用with as创建临时表以提高查询效率,case when的多种用法在条件判断中的应用,merge into语句的灵活性,以及分析函数over()的ROW_NUMBER()、RANK()和DENSE_RANK()在排序和分组中的功能。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

1) with as

a) WITH AS短语,也叫做子查询部分(subquery factoring),可以定义一个SQL片断,该SQL片断会被整个SQL语句所用到。

b) 当查询中多次用到某一部分时,可以用Oracle with语句创建一个公共临时表。因为子查询在内存临时表中,避免了重复解析,所以执行效率会提高不少。临时表在一次查询结束自动清除。

c) 应用:需多次调用;union all中;

d) 示例

with
    sql1 as (select s_name from test_tempa),
    sql2 as (select s_name from test_tempb where not exists (select s_name from sql1))
select * from sql1
union all
select * from sql2
union all
select 'no records' from dual
      where not exists (select s_name from sql1 where)
               and not exists (select s_name from sql2);

2) case when

a) 简单case语句

select case to_char(sysdate,’d’) 
when1then ‘周日’
when2then ‘周一’ 
else ‘不知道’
end 
from dual;

b) 搜索case语句

SELECT   count(col3), sum(CASE WHEN col3 = 1 THEN 1   
                       ELSE 0
                       END) ,
                sum(CASE WHEN col3 = 2 THEN 1
                       ELSE 0
                       END) 
    FROM table_name;

c) where case when 用法

SELECT T2.*, T1.*
   FROM T1, T2
  WHERE (CASE WHEN T2.col2 = 'A' AND
                   T1.col3 = 'B'
                THEN 1
              WHEN T2.col2 != 'A' AND
                   T1.col3 != 'B'
                THEN 1
              ELSE 0
           END) = 1

d) group by case when 用法

SELECT  CASE 
WHEN col2 = '05' THEN '1'  
WHEN col2 = '23'  THEN '2'  
ELSE NULL END "类别",
COUNT(*)  
FROM  table_name
GROUP BY  CASE 
WHEN col2 = '05' THEN '1'  
WHEN col2 = '23'  THEN '2'  
ELSE NULL END;

3) merge into

在oracle 11g中有如下特点
a) update和insert子句时可选的。

b) update和insert子句可以加where子句

merge into table_a a
using table_b b
on (a.id = b.id)
when matched then
        update set a.name = b.name where ……
when not matched then
        insert values (a.id,a.name) where ……

4) 分析函数over()

a) ROW_NUMBER(),不允许并列名次、相同值名次不重复,结果如123456……

b) RANK(),跳跃排序,允许并列名次、复制名次自动空缺,结果如12245558……

c) DENSE_RANK(),允许并列名次、名次不间断,结果如122344456……

d) 例子:

select * from (
selelct row_number() over(partition by xxx2 order by xxx3 ) rw from dual)tt where tt.rw = ?;
Oracle 数据库中,并不存在直接名为“上移函数”的特定函数,但如果理解为一种需求场景&mdash;&mdash;例如调整行顺序或将某一行的数据向上移动等操作,那么这可以通过多种组合技术来实现,比如使用 `ROW_NUMBER()`, `LAG()` 或者自定义 PL/SQL 块完成。 下面是几个可能满足“上移”含义的例子: --- ### 场景一:通过 LAG 窗口函数获取上方记录 假如你想取得当前记录对应的前一条记录(即“上移一位”的效果),可以借助窗口函数 `LAG()` 实现。 #### 示例代码: ```sql SELECT id, name, score, LAG(score, 1) OVER (ORDER BY id) AS prev_score -- 取得上一行score值 FROM students; ``` 这里 `LAG(score, 1)` 意味着从排序的结果集中取出前面第1条记录的分数作为新字段显示出来。你可以依据业务逻辑更改偏移量或 ORDER BY 条件适应实际需要。 --- ### 场景二:交换相邻两条记录位置 对于某些表格结构设计存在固定序号列的情况,若希望手动模拟物理意义上的上下调动某个项目的次序,则需要用到更新语句配合子查询定位目标项与其邻居的关系并互换它们的关键标识数值。 假设有张表 student_orders(id int primary key ,student_id varchar2(50), order_num number); 现在想让order_num=3的学生上升至第二位: #### 更新步骤如下所示: 先确认待修改的目标id和对应上面那笔资料的信息: ```sql WITH target_info AS ( SELECT * FROM( SELECT ROW_NUMBER() OVER (PARTITION BY STUDENT_ID ORDER BY ORDER_NUM ASC ) rn , ID,SUBJECT_NAME,ORDER_NUM FROM STUDENT_ORDERS WHERE STUDENT_ID='ABC') temp WHERE RN IN(2,3)) UPDATE STUDENT_ORDERS SO SET SO.ORDER_NUM= CASE WHEN EXISTS(SELECT TI.ID FROM TARGET_INFO TI WHERE ROWNUM<=>2 AND SO.ID =TI.ID ) THEN (SELECT MAX(TI.ORDER_NUM)-MIN(TI.ORDER_NUM)+MAX(TI.ORDER_NUM) FROM DUAL JOIN TARGET_INFO TI ON TRUE) ELSE MIN_VALUE END ; ``` 注意上述仅为示意思路,请根据实际情况编写适合自己的程序段落. --- ### 场景三:整体重新排列所有项目 如果有明确的新规则想要完全改变现有列表布局的话,可以直接生成全新的编号体系覆盖旧有的那个属性列即可达到目的. ```sql MERGE INTO my_table mt USING ( SELECT rownum new_order, t.* FROM ( SELECT * FROM my_table ORDER BY some_column DESC /* 自己设定排序条件 */ )t ) x ON (mt.id=x.id) WHEN MATCHED THEN UPDATE SET mt.order_col = x.new_order; ``` 此处采用的是merge语法把原表与经过再次组织过的临时视图进行合并运算,从而有效达成刷新整个队列的目的.
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值