google搜索前排，就有还不错的在线练习平台，浅学了一下
包括常用的函数，子句和表连接，但是对复杂的连表查询和子查询涉及的较少，需要进一步学习
笔记：
-- 1. SELECT
SELECT *
FROM labs_sqlexcustominfo
WHERE party_id="020001234567891"
-- 2. WHERE and AND
SELECT name, zone_num, branch_num, mobile
FROM labs_sqlexcustominfo
WHERE zone_num=0200 AND branch_num=1111
-- 3. WHERE and OR
SELECT name, zone_num, gender_cd, total_asset/10000 as assert
FROM labs_sqlexcustominfo
WHERE zone_num='0200' OR zone_num='3000'
-- 4. AND and OR
SELECT name, zone_num, gender_cd, total_asset/10000 as asset
FROM labs_sqlexcustominfo
WHERE total_asset >= 1000000 AND (zone_num='0200' OR zone_num='3000')
-- 5. case.. when...then...end
SELECT NAME,
CASE WHEN gender_cd='1' THEN '男'
WHEN gender_cd='2' THEN '女'
ELSE '未知'
END AS gender
FROM labs_sqlexcustominfo
WHERE zone_num='0200'
-- 6. other col using case
SELECT party_id,
name,
total_asset,
CASE
WHEN total_asset<5000 THEN '普通客户'
WHEN total_asset<50000 THEN '潜力客户'
WHEN total_asset<200000 THEN '中端客户A'
WHEN total_asset<1000000 THEN '中端客户B'
WHEN total_asset<8000000 THEN '高端客户'
ELSE '私人银行客户'
END AS level
FROM labs_sqlexcustominfo
-- 7. sum
SELECT SUM(total_asset)/10000 as sum_asset
FROM labs_sqlexcustominfo
WHERE zone_num='0200'
-- 8. count
SELECT COUNT(*) as number
FROM labs_sqlexcustominfo
WHERE gender_cd=1 AND zone_num='0200'
-- 9. max, min, avg
SELECT max(total_asset) as max_asset,
min(total_asset) as min_asset,
avg(total_asset) as avg_asset
FROM labs_sqlexcustominfo
WHERE zone_num='0200'
-- 10. strsub
SELECT substr(name, 1, 1) as family_name
FROM labs_sqlexcustominfo
WHERE zone_num='0200'
-- 11. group by 1
SELECT zone_num, SUM(total_asset) as asset
FROM labs_sqlexcustominfo
GROUP BY zone_num
-- 12. case, sum, group by
SELECT CASE WHEN birth_dt<'1980-1-1' THEN '80前' ELSE '80后' END as 年龄段,
SUM(CASE WHEN gender_cd='1' THEN 1 ELSE 0 END) AS 男性数量,
SUM(CASE WHEN gender_cd='2' THEN 1 ELSE 0 END) AS 女性数量
FROM labs_sqlexcustominfo
GROUP BY 年龄段
-- 13. 多字段group
SELECT zone_num, branch_num,
count(*) as number,
sum(total_asset),
avg(total_asset),
max(total_asset),
min(total_asset)
FROM labs_sqlexcustominfo
GROUP BY zone_num, branch_num
-- 14. having子句
-- 必须放在group by 后面
SELECT zone_num, branch_num,
count(*) as number,
sum(total_asset),
avg(total_asset),
max(total_asset),
min(total_asset)
FROM labs_sqlexcustominfo
GROUP BY zone_num, branch_num
HAVING number>1
-- 15. order by
SELECT *
FROM labs_sqlexcustominfo
WHERE zone_num='0200'
ORDER BY total_asset
-- 16. order asc, desc
SELECT zone_num, branch_num,
sum(total_asset) as sum_asset
FROM labs_sqlexcustominfo
GROUP BY zone_num, branch_num
ORDER BY zone_num asc,
sum_asset desc
-- 17. inner join
SELECT a.cust_id, asset, loan,
(asset-loan) as net
FROM labs_sqlexasset as a
INNER JOIN labs_sqlexloan as b
on a.cust_id=b.cust_id
-- 18. 不等值连接条件
SELECT a.cust_id, asset, loan
FROM labs_sqlexasset as a
INNER JOIN labs_sqlexloan as b
on a.cust_id=b.cust_id and asset>loan
-- 19. left join
-- order by 1 表示按照第一个字段排序
SELECT a.cust_id, asset, loan
FROM labs_sqlexasset as a
LEFT JOIN labs_sqlexloan as b
ON a.cust_id=b.cust_id
ORDER BY 1
-- 20. 多表left join
-- coalesec
SELECT a.cust_id,
product,
asset,
coalesce(c.loan, 0)
FROM labs_sqlexproduct as a
LEFT JOIN labs_sqlexasset as b
ON a.cust_id=b.cust_id
LEFT JOIN labs_sqlexloan as c
ON a.cust_id=c.cust_id
WHERE product>=3
ORDER BY product DESC
-- 21. union
SELECT cust_id
FROM labs_sqlexasset as a
UNION
SELECT cust_id
FROM labs_sqlexloan as b
-- 22. 嵌套子查询
SELECT a.party_id,
a.name,
b.asset_cnt,
b.asset_bal
FROM labs_sqlexcustominfo as a
INNER JOIN (
SELECT cust_id, count(*) as asset_cnt,
sum(bal) as asset_bal
FROM labs_sqlexcustomasset
WHERE asset_type not in ('三方存管', '保险')
GROUP BY cust_id
HAVING asset_cnt>1
) as b
ON a.cust_id=b.cust_id
ORDER BY asset_bal DESC