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

最新推荐文章于 2025-05-14 18:05:25 发布

原创最新推荐文章于 2025-05-14 18:05:25 发布 · 5.3k 阅读

8 ·

CC 4.0 BY-SA版权

文章标签：

#oracle

oracle 专栏收录该内容

18 篇文章

订阅专栏

这篇博客详细介绍了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’) 
when ‘1’then ‘周日’
when ‘2’ then ‘周一’ 
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 = ?;