《SQL学习指南》

第9章

SELECT groups.name, COUNT(*) num_customers
FROM (
SELECT SUM(a.avail_balance) cust_balance
FROM account a INNER JOIN product p
on a.product_cd = p.product_cd
WHERE p.product_type_cd = 'account'
GROUP BY a.cust_id ) cust_rollup
INNER JOIN
(SELECT 'small fry' name, 0 low_limit, 4999.00 high_limit
UNION ALL
SELECT 'Average Joes' name, 5000 low_limit, 9999.99 high_limit
UNION ALL
SELECT 'heavy hitters' name, 10000 low_limit, 999999.99 high_limit
 ) groups
ON cust_rollup.cust_balance
BETWEEN groups.low_limit AND groups.high_limit
GROUP BY groups.name

 

SELECT p.name product,b.name branch, concat(e.fname, ' ', e.lname) name,
account_groups.tot_deposits
FROM
(SELECT product_cd,open_branch_id branch_id,open_emp_id emp_id, SUM(avail_balance) tot_deposits
 FROM account
 GROUP BY product_cd,open_branch_id, open_emp_id) account_groups
 INNER JOIN employee e ON e.emp_id =    account_groups.emp_id
 INNER JOIN branch b ON b.branch_id =   account_groups.branch_id
 INNER JOIN product p ON p.product_cd =  account_groups.product_cd
 WHERE p.product_type_cd = 'account'

　　第十章

下面代码综合了交叉连接、外连接、日期函数、分组、集合运算符（union all）、聚合函数

SELECT days.dt, COUNT(t.txn_id)>0 FROM transaction t RIGHT OUTER JOIN
(SELECT date_add('2008-01-01',
	INTERVAL(ones.num + tens.num + hundreds.num) day) dt
FROM
(SELECT 0 num UNION ALL
 SELECT 1 num UNION ALL
 SELECT 2 num UNION ALL
 SELECT 3 num UNION ALL
 SELECT 4 num UNION ALL
 SELECT 5 num UNION ALL
 SELECT 6 num UNION ALL
 SELECT 7 num UNION ALL
 SELECT 8 num UNION ALL
 SELECT 9 num) ones
 CROSS JOIN
 (SELECT 0 num UNION ALL
 SELECT 10 num UNION ALL
 SELECT 20 num UNION ALL
 SELECT 30 num UNION ALL
 SELECT 40 num UNION ALL
 SELECT 50 num UNION ALL
 SELECT 60 num UNION ALL
 SELECT 70 num UNION ALL
 SELECT 80 num UNION ALL
 SELECT 90 num) tens
 CROSS JOIN
 (SELECT 0 num UNION ALL
 SELECT 100 num UNION ALL
 SELECT 200 num UNION ALL
 SELECT 300 num) hundreds
 WHERE date_add('2008-01-01', INTERVAL(ones.num + tens.num + hundreds.num) day) < '2009-01-01') days
 ON days.dt = t.txn_date
 GROUP BY days.dt
 ORDER BY 1