SQL练习第一题

SQL练习第一题

题目

表名：macro_index_data
字段名：
数据期（年月）   地区代码     指标代码   指标类型 (增速、总量） 指标值       数据更新时间
occur_period  area_code  index_code  index_type       index_value  update_time

说明：罗湖区的区划代码为 440305000000、GDP指标代码为gmjj_jjzl_01、指标类型的枚举值分别是增速(TB)、总量(JDZ)

问题

请写出，2020年4个季度中GDP的增速都超过罗湖区同期的区有哪些

答案

-- 求出罗湖区2020年4个季度的GDP增速
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

-- 求出其他区中的GDP增速
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

-- 整合函数
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;