2021-08-31

天池龙珠sql训练营Task06

6.1
select a.TICKER_SYMBOL, a.END_DATE, a.CLOSE_PRICE, b.TICKER_SYMBOL, b.INDIC_NAME_EN, b.END_DATE, b.VALUE, c.TICKER_SYMBOL, c.END_DATE, c.T_REVENUE, c.T_COGS,
c.N_INCOME from market data a left join company operating b on a.TICKER_SYMBOL = b.TICKER_SYMBOL
left join income statement c on a.TICKER_SYMBOL = c.TICKER_SYMBOL where a.TICKER_SYMBOL in (‘600383’, ‘600048’);

6.2 中式排名
SELECT
c.rownum as ‘acid排行(从高到低)’,
d.*
FROM
winequality-red d
LEFT JOIN (
SELECT
a.citric acid,
@rownum := @rownum + 1 AS rownum
FROM
( SELECT citric acid FROM winequality-red WHERE pH = ‘3.03’ GROUP BY citric acid ORDER BY citric acid DESC ) a,
( SELECT @rownum := 0 ) b
) c ON c.citric acid = d.citric acid
WHERE
d.pH = ‘3.03’
ORDER BY
c.rownum;
6.3
– 数量最多
select Merchant_id, count(1) n
from ccf_offline_stage1_test_revised a where DATE_FORMAT(a.Date_received,’%Y.%m’) = ‘2016.07’ group by a.Merchant_id order by a.n desc limit 1;
– 金额最多
select Merchant_id, sum(case when a.Discount_rate like ‘%:%’ then substring_index(a.Discount_rate, ‘:’,-1) else 0 end) as s from ccf_offline_stage1_test_revised a group by a.Merchant_id order by s desc limit 1;