阿里云——天池SQL训练营Task06

本笔记为阿里云天池龙珠计划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

代码编写：

SELECT DISTINCT
    MD.*,
    CO.INDIC_NAME_EN,
    CO.VALUE,
    incom.N_INCOME,
    incom.T_COGS,
    incom.T_REVENUE 
FROM
    ( SELECT TICKER_SYMBOL, END_DATE, CLOSE_PRICE FROM `market data` WHERE TICKER_SYMBOL = 600383 OR TICKER_SYMBOL = 600048  ) MD # market data
    LEFT JOIN
    ( SELECT TICKER_SYMBOL, INDIC_NAME_EN, END_DATE, VALUE FROM `company operating` WHERE TICKER_SYMBOL = 600383 OR TICKER_SYMBOL = 600048  ) CO ON MD.TICKER_SYMBOL = CO.TICKER_SYMBOL AND MD.END_DATE = CO.END_DATE # company operating
    LEFT JOIN
    ( SELECT TICKER_SYMBOL, END_DATE, T_REVENUE, T_COGS,