mosh-10小时SQL

-- 3小时结束，另外7小时

SELECT
　　MAX(invoice_total) highest,
　　MIN(invoice_total) lowest,
　　AVG(invoice_total) average,
　　SUM(invoice_total) total, -- 可以写表达式 SUM(invoice_total) * 1.1)
　　COUNT(invoice_total) number_of_invoices,
　　COUNT(DISTINCT client_id),-- 记录唯一值
　　COUNT(payment_date) number_of_payment, -- 忽略空值
　　COUNT(*) total_records -- 包含空值
FROM invoices
WHERE invoice_date > '2019-07-01';

-- EXERCISE
SELECT
　　'First half of 2019' date_range,
　　SUM(invoice_total) total_sales,
　　SUM(payment_total) total_payment,
　　SUM(invoice_total - payment_total) what_we_expect
FROM invoices
WHERE invoice_date BETWEEN '2019-01-01' AND '2019-06-30'
UNION
SELECT
　　'Second half of 2019' date_range,
　　SUM(invoice_total) total_sales,
　　SUM(payment_total) total_payment,
　　SUM(invoice_total - payment_total) what_we_expect
FROM invoices
WHERE invoice_date BETWEEN '2019-07-01' AND '2019-12-31'
UNION
SELECT
　　'Total' date_range,
　　SUM(invoice_total) total_sales,
　　SUM(payment_total) total_payment,
　　SUM(invoice_total - payment_total) what_we_expect
FROM invoices
WHERE invoice_date BETWEEN '2019-01-01' AND '2019-12-31';
-- 聚合函数汇总数据

SELECT
　　p.date,
　　pm.name payment_method,
　　SUM(p.amount) total_payments
FROM payments p
JOIN payment_methods pm
ON p.payment_method = pm.payment_method_id
GROUP BY date,payment_method
ORDER BY date;
-- GROUP BY数据分组

SELECT
　　c.customer_id,
　　c.first_name,
　　c.last_name,
SUM(oi.quantity * oi.unit_price) AS total_sales
FROM customers c
JOIN orders o USING(customer_id)
JOIN order_items oi USING(order_id)
WHERE c.state = 'VA'
GROUP BY
　　c.customer_id,
　　c.first_name,
　　c.last_name
HAVING total_sales > 100;
-- HAVING 过滤分组结果，放置在GROUP BY后,后接字段须是GROUP BY出现过的字段

SELECT
　　pm.name payment_method,
　　SUM(amount) total
FROM payments p
JOIN payment_methods pm
ON p.payment_method = pm.payment_method_id
GROUP BY pm.name WITH ROLLUP;
-- WITH ROLLUP 汇总聚合函数,用在GROUP BY之后，如果别名是表中已经出现过的，WITH ROLLUP前使用实际字段名，不能使用别名；如果别名是表中没有的字段，可以使用别名。