SQL 必知必会第十一课 使用子查询

SQL 必知必会第十一课 使用子查询

select order_num from orderitems where prod_id = 'RGAN01';
select cust_id from orders where order_num in (20007,20008);

##结合两句查询
select cust_id from orders where order_num in 
(select order_num from orderitems where prod_id = 'RGAN01');

select cust_name, cust_contact from customers where cust_id in
(select cust_id from orders where order_num in 
(select order_num from orderitems where prod_id = 'RGAN01'));

select count(*) as orders from orders where cust_id = 1000000001;

select cust_name, 
	   cust_state, 
       (select count(*) 
        from orders 
        where orders.cust_id = customers.cust_id)  as orders
from customers 
order by cust_name;

## 错误例子
select cust_name, 
	   cust_state, 
       (select count(*) 
        from orders 
        where cust_id = cust_id)  as orders -- 错误，因为没有限制列名
from customers 
order by cust_name;

## challenges

select cust_id from  orders where order_num in
(select order_num from orderitems where item_price >= 10 ); -- 1

select cust_id, 
       order_date
from   orders
where cust_id in 
                (select cust_id from orderitems where prod_id = 'BR01')
order by order_date; -- 2

select cust_email
from customers 
where cust_id in
(select cust_id from orders where cust_id in 
                (select cust_id from orderitems where prod_id = 'BR01')); -- 3

## 第四题
select cust_id,
		(select sum(quantity * item_price) -- sum和括号中不要有空格
		from OrderItems
		where OrderItems.order_num = orders.order_num
		group by order_num) as total_ordered-- 第二步选出价格
from orders
order by total_ordered;-- 第一步选出顾客id

## 第五题
select prod_name,
       (select sum(quantity)
        from OrderItems
        where orderitems.prod_id = products.prod_id
        group by prod_id) as quant_sold -- 找出卖掉的产品数量
from products;  -- 找出对应的产品名称

注意点

1. 子查询只能查询单列

2. 列名要完全限定，错误例子见代码