Oracle高级查询技巧

以下是个人对一些常用的高级查询技巧的一番梳理，表很简单，就不提供脚本了。

--1. 使用集合操作符：将两个或多个查询返回的行组合起来
select * from product_types;
select * from products;
select * from more_products;

-- 使用union all操作符：返回查询所检索出的所有行，包括重复行
select product_id, product_type_id, product_name from products
union all
select prd_id, prd_type_id, prd_name from more_products order by 1;

-- 修改数据
update more_products set prd_name = '绿茶' where prd_id = 1;
select * from more_products for update;

-- 使用union 操作符：返回查询所检索出的所有非重复行
select product_id, product_type_id, product_name from products
union
select prd_id, prd_type_id, prd_name from more_products order by 1;

-- 使用intersect操作符：返回两个查询所检索出的共有行
select product_id, product_type_id, product_name from products
intersect
select prd_id, prd_type_id, prd_name from more_products order by 1;

-- 使用minus操作符：返回将第二个查询检索出的行从第一个检索出的行中减去之后剩余的行（即第一个查询检索出的行减去共有行）
select product_id, product_type_id, product_name from products
minus
select prd_id, prd_type_id, prd_name from more_products order by 1;

-- 组合使用集合操作符：集合操作符默认是从上至下求值，最好用括号
(select product_id, product_type_id, product_name from products
union
select prd_id, prd_type_id, prd_name from more_products)
intersect
select product_id, product_type_id, product_name from products_changes;

-- 使用translate(x, from_string, to_string)函数
select translate('select', 'select', 'xwq') from dual;
select product_id, product_name, translate(product_name, '绿茶哇生包' ,'abcdf') from products

-- 使用decode(value,search_value,result,defaul_result)函数：对value和search_value进行比较，若相等返回result，否则返回defaul_result
select prd_id, prd_type_id, available, decode(available, '1', 'available', 'not available')
from more_products;

-- decode()函数：传递多个搜索和结果参数
select prd_id, available, decode(prd_type_id, 1, '饮料', 2, '食品', '其它')
from more_products;

--===============================使用case表达式===============================
-- 使用简单case表达式
select product_id,
       product_type_id,
       case product_type_id
         when 1 then
          '饮料'
         when 2 then
          '食品'
         else
          '其它'
       end as product_type_name
  from products;
-- 使用搜索case表达式
  select product_id,
       product_type_id,
       case
         when product_type_id = 1 then
          '饮料'
         when product_type_id = 2 then
          '食品'
         else
          '其它'
       end as product_type_name
  from products;
 
-- 在搜索case表达式中可以用操作符
select product_id,product_type_id, price,
    case
    when price < 30 and price > 0 then '便宜'
    when price < 90 and price > 30 then '还好'
    else '好贵'
    end as price_level
from products;

--===============================层次化查询===============================
-- 使用connect by和start with子句
select emploee_id, manager_id, first_name, last_name
from more_employees
start with emploee_id = 1
connect by prior emploee_id = manager_id;

-- 使用伪列level
select level, emploee_id, manager_id, first_name, last_name
from more_employees
start with emploee_id = 1
connect by prior emploee_id = manager_id
order by level;
-- 查询层次数
select count(distinct level) from more_employees
start with emploee_id = 1
connect by prior emploee_id = manager_id;
-- 格式化层次化查询的结果：用level和lpad函数
select level, lpad(' ', 2*level - 1) || first_name || '  ' || last_name as employee
from more_employees
start with emploee_id = 1
connect by prior emploee_id = manager_id;
-- 从非节点开始遍历
select level, lpad(' ', 2*level - 1) || first_name || '  ' || last_name as employee
from more_employees
start with emploee_id = 3
connect by prior emploee_id = manager_id;
-- 在start with中使用子查询
select level, lpad(' ', 2*level - 1) || first_name || '  ' || last_name as employee
from more_employees
start with emploee_id = (select emploee_id from more_employees where first_name = 'Kevin'
and last_name = 'Black')
connect by prior emploee_id = manager_id;
-- 从下向上遍历树：交换父节点和子节点在connect by prior中的顺序
select level, lpad(' ', 2*level - 1) || first_name || '  ' || last_name as employee
from more_employees
start with emploee_id = (select emploee_id from more_employees where first_name = 'Kevin'
and last_name = 'Black')
connect by prior manager_id = emploee_id;
-- 从层次查询中删除节点和分支：可以利用where字句从查询树中进行过滤
select level, lpad(' ', 2*level - 1) || first_name || '  ' || last_name as employee
from more_employees
where last_name != 'Johnson'
start with emploee_id = 1
connect by prior emploee_id = manager_id;
-- 为了将Johnson下的节点全部删除，可以在connect by prior后接同样的条件
select level, lpad(' ', 2*level - 1) || first_name || '  ' || last_name as employee
from more_employees
where last_name != 'Johnson'
start with emploee_id = 1
connect by prior emploee_id = manager_id
and last_name != 'Johnson';
-- 在层次化查询中加入其它条件
select level, lpad(' ', 2*level - 1) || first_name || '  ' || last_name as employee
from more_employees
where salary < 600000
start with emploee_id = 1
connect by prior emploee_id = manager_id
and salary < 600000;