编写复杂查询-子查询

主要是对SELECT子查询进行嵌套使用

• 对于某些问题不容易解决, 可以考虑使用子查询

-- Find products that are more
-- expensive than Lettuce (id = 3)
USE sql_store;
SELECT *
FROM products
WHERE unit_price > (
	SELECT unit_price 
    FROM products
    WHERE product_id = 3
);
-- Exercise 
-- in sql_hr database:
-- Find employees whose earn  more than average
USE sql_hr;
SELECT *
FROM employees
WHERE salary > (
	SELECT AVG(salary)
    FROM employees
);
    
-- 子查询IN 
-- Find the products that have never been ordered

USE  sql_store;
SELECT *
FROM products
WHERE product_id NOT IN (
	SELECT DISTINCT product_id
	FROM order_items
);

-- Exercise
-- Find clients without invoices
USE sql_invoicing;

SELECT * 
FROM clients
WHERE client_id NOT IN (
	SELECT DISTINCT client_id
	FROM invoices
);

-- 子查询和连接
SELECT * 
FROM clients
LEFT JOIN invoices USING(client_id)
WHERE invoice_id IS NULL;
-- Find customers who have ordered  Lettuce (id = 3)
-- Select customer_id, first_name, last_name
USE sql_store;

SELECT 
	customer_id,
	first_name,
    last_name
FROM customers
WHERE customer_id IN (
	SELECT customer_id
	FROM orders
	WHERE order_id IN (
		SELECT order_id
		FROM order_items
		WHERE product_id = 3
));
-- 连接

SELECT 
	DISTINCT customer_id,
	first_name,
    last_name
FROM customers 
JOIN  orders o USING (customer_id)
JOIN order_items oi  USING(order_id)
WHERE oi.product_id = 3;

-- ALL 关键字
-- Select invoices larger than all invoices of
-- client 3
SELECT MAX(invoice_total)
FROM invoices
WHERE client_id = 3;

SELECT  *
FROM invoices
WHERE invoice_total > (
	SELECT MAX(invoice_total)
	FROM invoices
	WHERE client_id = 3
);
-- ALL 高于所有
SELECT  *
FROM invoices
WHERE invoice_total > ALL (
	SELECT (invoice_total)
	FROM invoices
	WHERE client_id = 3
);

-- ANY SOME 高于任何一个即可
SELECT  *
FROM invoices
WHERE invoice_total > ANY(
	SELECT (invoice_total)
	FROM invoices
	WHERE client_id = 3
);

-- SELECT clients with at least two invoices
USE sql_invoicing;
SELECT *
FROM clients 
WHERE client_id IN (
	SELECT 
		client_id
	FROM invoices
	GROUP BY client_id
	HAVING COUNT(*) >= 2
);

SELECT *
FROM clients 
WHERE client_id = ANY(
	SELECT 
		client_id
	FROM invoices
	GROUP BY client_id
	HAVING COUNT(*) >= 2
);
-- 相关子查询

-- Select employees whose salary is 
-- above the average in their office
USE sql_hr;
SELECT *
FROM employees e
WHERE salary > (
	SELECT 
		AVG(salary)
	FROM employees
	WHERE office_id = e.office_id
);

-- Exercise 
-- Get invoices that are larger than the
-- client's average invoice amount
USE sql_invoicing;

SELECT *
FROM invoices i
WHERE invoice_total >(
	SELECT 
	AVG(invoice_total)
	FROM invoices
    WHERE client_id = i.client_id
    );

-- Exist
-- Select clients that have an invoice
SELECT *
FROM clients
WHERE client_id IN(
	SELECT DISTINCT client_id
    FROM invoices
    );
    
SELECT *
FROM clients c
WHERE EXISTS(
      SELECT client_id
    FROM invoices
    WHERE client_id = c.client_id
    );

-- Find the products that have never been ordered

USE sql_store;
SELECT * 
FROM products
WHERE product_id NOT IN(
	SELECT product_id
    FROM order_items);
    
USE sql_store;
SELECT * 
FROM products p
WHERE NOT EXISTS (
	SELECT product_id
    FROM order_items
    WHERE product_id = p.product_id 
    );    
-- SELECT 子查询
USE sql_invoicing;
SELECT 
	invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total)
		FROM invoices) AS invoice_average,
	invoice_total - (SELECT invoice_average) AS difference
FROM invoices;

SELECT 
	client_id,
          name,
	(SELECT SUM(invoice_total) 
		FROM invoices
        WHERE client_id = c.client_id) AS total_sales,
	(SELECT AVG(invoice_total) FROM invoices) AS average,
	(SELECT total_sales -  average) AS difference
FROM clients c;

-- FROM 子语句查询
SELECT *
FROM (
	SELECT 
		client_id,
		name,
		(SELECT SUM(invoice_total) 
			FROM invoices
			WHERE client_id = c.client_id) AS total_sales,
		(SELECT AVG(invoice_total) FROM invoices) AS average,
		(SELECT total_sales -  average) AS difference
	FROM clients c
) AS sales_summary;