SQL exercise question 1

subject

 Table name ：macro_index_data
 Field name ：
 Data period （ years ）    Region code       The index code     The index type  ( growth 、 Total amount ）  Index value         Data update time 
occur_period  area_code  index_code  index_type       index_value  update_time

 explain ： The zoning code of Luohu District is  440305000000、GDP The indicator code is gmjj_jjzl_01、 The enumeration values of indicator types are respectively (TB)、 Total amount (JDZ)

problem

 Please write out ,2020 year 4 Quarterly GDP The growth rate of has exceeded that of Luohu District. What are the districts in the same period 

answer

--  Luohu District 2020 year 4 It's a quarter of GDP growth 
select 
area_code,
sum (case when month('occur_period') between 1 and 3 then index_value else 0 end)),
sum (case when month('occur_period') between 4 and 6 then index_value else 0 end)),
sum (case when month('occur_period') between 7 and 9 then index_value else 0 end)),
sum (case when month('occur_period') between 10 and 12 then index_value else 0 end))
from macro_index_data
where area_code = '440305000000'
and index_code = 'gmjj_jjzl_01'
and index_type = 'TB'
and year('occur_period') = 2020
group by area_code

--  Find the in other areas GDP growth 
select 
area_code,
sum (case when month('occur_period') between 1 and 3 then index_value else 0 end)),
sum (case when month('occur_period') between 4 and 6 then index_value else 0 end)),
sum (case when month('occur_period') between 7 and 9 then index_value else 0 end)),
sum (case when month('occur_period') between 10 and 12 then index_value else 0 end))
from macro_index_data
where area_code <> '440305000000'
and index_code = 'gmjj_jjzl_01'
and index_type = 'TB'
and year('occur_period') = 2020
group by area_code

--  Integration function 
with t1 as (
select 
area_code,
sum (case when month('occur_period') between 1 and 3 then index_value else 0 end)) `one`,
sum (case when month('occur_period') between 4 and 6 then index_value else 0 end)) `two`,
sum (case when month('occur_period') between 7 and 9 then index_value else 0 end)) `three`,
sum (case when month('occur_period') between 10 and 12 then index_value else 0 end) `four`)
from macro_index_data
where area_code = '440305000000'
and index_code = 'gmjj_jjzl_01'
and index_type = 'TB'
and year('occur_period') = 2020
group by area_code
),
t2 as (
select 
area_code,
sum (case when month('occur_period') between 1 and 3 then index_value else 0 end)) `one`,
sum (case when month('occur_period') between 4 and 6 then index_value else 0 end)) `two`,
sum (case when month('occur_period') between 7 and 9 then index_value else 0 end)) three`,
sum (case when month('occur_period') between 10 and 12 then index_value else 0 end)) `four`
from macro_index_data
where area_code <> '440305000000'
and index_code = 'gmjj_jjzl_01'
and index_type = 'TB'
and year('occur_period') = 2020
group by area_code
)
select t2.area_code
from 
t1 join t2 on t1.area_code = t2.area_code
where 
t1.one < t2.one and
t1.two < t2.two and
t1.three < t2.three and
t1.four < t2.four;