Oracle Database 11g SQL 开发指南学习笔记：子查询

 

--1.单行子查询
--查找那些均价不是最高的产品类的id和均价
select product_type_id,
       avg(price)
from products
group by product_type_id
having avg(price) < (select max(avg(price))
                     from products
                     group by product_type_id
                    ) --单行子查询，也就是返回一行的子查询
order by product_type_id;


with p
as
(
select product_type_id,
       avg_price,
       rank() over(order by avg_price desc) rank_num
from 
(
select product_type_id,
       avg(price) as avg_price
from products
group by product_type_id
)
)

select *
from p
where rank_num >= 2;


--每个产品的购买次数
select pd.product_id,
       pd.price,
       p.purchase_count
from products pd
inner join 
  (
    select product_id,
           count(*) as purchase_count
    from purchases
    group by product_id
  )p  --内联视图
  on p.product_id = pd.product_id

        
--子查询不能包含order by,下面的语句会报错
select *
from products
where price > (select avg(price) 
               from products 
               order by product_id);
               

--2.多行子查询
--购买过的产品名称包含字母e的产品
select *
from products
where name like '%e%'
      and product_id in (select product_id from purchases);


--没有购买过的产品
select 
from products
where product_id not in (select product_id from purchase);


--员工的工资低于任意一个工资级别的最低工资
select employee_id,
       last_name,
       salary
from employees
where salary <  any(select low_salary from salary_grades);


--员工的工资高于所有工资级别的最高工资
select employee_id,
       last_name,
       salary
from employees
where salary > all(select high_salary from salary_grades);


--3.多列子查询
--查询那些产品价格是所在产品类中最低的产品
select *
from products
where (product_type_id,price) in （select product_type_id,min(price)
                                  from products
                                  group by product_type_id
                                  ）;


--4.相关子查询
--找出产品价格大于同类产品的均价的产品
select product_id,
       product_type_id,
       name,
       price
from products p1
where price > (select avg(price) 
               from products p2 
               where p1.product_type_id = p2.product_type_id);


select p1.product_id,
       p1.product_type_id,
       p1.name,
       p1.price
from products p1
inner join 
      (
        select product_type_id,
               avg(price) as avg_price
        from products 
        group by product_type_id
      )p2
      on p2.product_type_id = p1.product_type_id
         and p1.price > p2.avg_price;


--有下属的人员
select employee_id,
       last_name
from employees e
where exists (select 1 
              from employees ee
              where e.employee_id = ee.manager_id);


select distinct 
       e.employee_id,
       e.last_name
from employees e
inner join employees ee
        on e.employee_id = ee.manager_id


--没有购买过的产品
select p.product_id,
       p.name
from products p
where not exists(select 1 
                 from purchases pa
                 where pa.product_id = p.product_id);

select p.product_id,
       p.name
from products p
where product_id not in(select product_id 
                        from purchases pa);


--找出不包含任何产品的产品类型
select *
from product_types pt
where not exists (select 1 
                  from products p
                  where p.product_type_id = pt.product_type_id );

--这个表面看没什么问题，但其实是错误的，因为这里的多行子查询的返回值中包含了null
select *
from product_types pt
where product_type_id not in (select product_type_id from products); 

--正确的
select *
from product_types pt
where product_type_id not in (select nvl(product_type_id,0) from products); 


--5.嵌套子查询
--查找出那些产品所属品类的均价小于，多次购买的产品所属品类中最高的均价，的产品的品类和均价
select product_type_id,
       avg(price)
from products
group by product_type_id
having avg(price) < (select max(avg(price))
                     from products
                     where product_id in (select product_id
                                          from purchases
                                          where quantity > 1)
                     group by product_type_id);
                     

--6.包含子查询的update、delete语句
--把员工号为4的员工的工资，修改为所有工资级别的最高工资的平均值
update employees
set salary = (select avg(high_salary)
              from salary_grades)
where employee_id = 4;


--删除工资大于所有工资级别的最高工资的平均值的员工
delete from employees
where salary > (select avg(high_salary)
                from salary_grades
               );