【学习笔记】阿里云天池龙珠计划SQL训练营-Task06：综合练习题-10道经典题目

 本笔记为阿里云天池龙珠计划SQL训练营的学习内容

链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql

练习题1：

请使用A股上市公司季度营收预测数据集《Income Statement.xls》和《Company Operating.xlsx》和《Market Data.xlsx》，以Market Data为主表，将三张表中的TICKER_SYMBOL为600383和600048的信息合并在一起。只需要显示以下字段。

表名	字段名
Income Statement	TICKER_SYMBOL
Income Statement	END_DATE
Income Statement	T_REVENUE
Income Statement	T_COGS
Income Statement	N_INCOME
Market Data	TICKER_SYMBOL
Market Data	END_DATE_
Market Data	CLOSE_PRICE
Company Operating	TICKER_SYMBOL
Company Operating	INDIC_NAME_EN
Company Operating	END_DATE
Company Operating	VALUE

解题思路：先从三张表中找出TICKER_SYMBOL为600383和600048的信息，在以Market Data为主表，采用left join对三表进行合并

SELECT * FROM 
(SELECT TICKER_SYMBOL,END_DATE,CLOSE_PRICE from `market data` where TICKER_SYMBOL in('600383','600048')) as m 
left JOIN
(select TICKER_SYMBOL,END_DATE,T_REVENUE,T_COGS,N_INCOME FROM `income statement` where TICKER_SYMBOL in ('600383','600048')) as i
on m.TICKER_SYMBOL = i.TICKER_SYMBOL AND m.END_DATE = i.END_DATE
left JOIN
(SELECT TICKER_SYMBOL,INDIC_NAME_EN,END_DATE,`VALUE` from `company operating` where TICKER_SYMBOL in('600383','600048')) as c
on m.TICKER_SYMBOL = c.TICKER_SYMBOL and m.END_DATE = c.END_DATE

练习题2：

请使用 Wine Quality Data 数据集《winequality-red.csv》，找出 pH=3.03的所有红葡萄酒，然后，对其 citric acid 进行中式排名（相同排名的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”）<