83 lines
5.0 KiB
SQL
83 lines
5.0 KiB
SQL
|
|
select
|
|
formatDateTime( REGISTER_DATE, '%Y-%m' ) as Year_Month,
|
|
SUM(case when AREA_NAME = '徐汇区' then 1 else 0 end ) as `徐汇区`,
|
|
SUM(case when AREA_NAME = '崇明区' then 1 else 0 end ) as `崇明区`,
|
|
SUM(case when AREA_NAME = '静安区' then 1 else 0 end ) as `静安区`,
|
|
SUM(case when AREA_NAME = '松江区' then 1 else 0 end ) as `松江区`,
|
|
SUM(case when AREA_NAME = '长宁区' then 1 else 0 end ) as `长宁区`,
|
|
SUM(case when AREA_NAME = '闵行区' then 1 else 0 end ) as `闵行区`,
|
|
SUM(case when AREA_NAME = '黄浦区' then 1 else 0 end ) as `黄浦区`,
|
|
SUM(case when AREA_NAME = '金山区' then 1 else 0 end ) as `金山区`,
|
|
SUM(case when AREA_NAME = '杨浦区' then 1 else 0 end ) as `杨浦区`,
|
|
SUM(case when AREA_NAME = '嘉定区' then 1 else 0 end ) as `嘉定区`,
|
|
SUM(case when AREA_NAME = '普陀区' then 1 else 0 end ) as `普陀区`,
|
|
SUM(case when AREA_NAME = '宝山区' then 1 else 0 end ) as `宝山区`,
|
|
SUM(case when AREA_NAME = '浦东新区' then 1 else 0 end ) as `浦东新区`,
|
|
SUM(case when AREA_NAME = '奉贤区' then 1 else 0 end ) as `奉贤区`,
|
|
SUM(case when AREA_NAME = '青浦区' then 1 else 0 end ) as `青浦区`,
|
|
SUM(case when AREA_NAME = '虹口区' then 1 else 0 end ) as `虹口区`
|
|
from
|
|
TSJB_DATA td
|
|
group by
|
|
formatDateTime(REGISTER_DATE, '%Y-%m')
|
|
order by
|
|
Year_Month desc;
|
|
|
|
|
|
|
|
-- 执法检查季度_统一信用代码去重
|
|
select
|
|
sub.Year,
|
|
SUM(case when sub.BUREAU_UNIT_NAME like '%徐汇%' then 1 else 0 end) as `徐汇区`,
|
|
SUM(case when sub.BUREAU_UNIT_NAME like '%崇明区%' then 1 else 0 end) as `崇明区`,
|
|
SUM(case when sub.BUREAU_UNIT_NAME like '%静安区%' then 1 else 0 end) as `静安区`,
|
|
SUM(case when sub.BUREAU_UNIT_NAME like '%松江区%' then 1 else 0 end) as `松江区`,
|
|
SUM(case when sub.BUREAU_UNIT_NAME like '%长宁区%' then 1 else 0 end) as `长宁区`,
|
|
SUM(case when sub.BUREAU_UNIT_NAME like '%闵行区%' then 1 else 0 end) as `闵行区`,
|
|
SUM(case when sub.BUREAU_UNIT_NAME like '%黄浦区%' then 1 else 0 end) as `黄浦区`,
|
|
SUM(case when sub.BUREAU_UNIT_NAME like '%金山区%' then 1 else 0 end) as `金山区`,
|
|
SUM(case when sub.BUREAU_UNIT_NAME like '%杨浦区%' then 1 else 0 end) as `杨浦区`,
|
|
SUM(case when sub.BUREAU_UNIT_NAME like '%嘉定区%' then 1 else 0 end) as `嘉定区`,
|
|
SUM(case when sub.BUREAU_UNIT_NAME like '%普陀区%' then 1 else 0 end) as `普陀区`,
|
|
SUM(case when sub.BUREAU_UNIT_NAME like '%宝山区%' then 1 else 0 end) as `宝山区`,
|
|
SUM(case when sub.BUREAU_UNIT_NAME like '%浦东新区%' or sub.BUREAU_UNIT_NAME like '%自由贸易%' then 1 else 0 end) as `浦东新区`,
|
|
SUM(case when sub.BUREAU_UNIT_NAME like '%奉贤区%' then 1 else 0 end) as `奉贤区`,
|
|
SUM(case when sub.BUREAU_UNIT_NAME like '%青浦区%' then 1 else 0 end) as `青浦区`,
|
|
SUM(case when sub.BUREAU_UNIT_NAME like '%虹口区%' then 1 else 0 end) as `虹口区`
|
|
from
|
|
(
|
|
select
|
|
BUREAU_UNIT_NAME,
|
|
ENT_USCC,
|
|
year(START_TIME) as Year
|
|
from
|
|
BL.YDJG_INSP
|
|
group by
|
|
BUREAU_UNIT_NAME,
|
|
ENT_USCC,
|
|
year(START_TIME)
|
|
) as sub
|
|
group by
|
|
sub.Year
|
|
order by
|
|
sub.year
|
|
|
|
|
|
SUM(case when BUREAU_UNIT_NAME like '%徐汇%' or ADDR like '%徐汇%' then 1 else 0 end) as `徐汇区`,
|
|
SUM(case when BUREAU_UNIT_NAME like '%崇明%' or ADDR like '%崇明%' then 1 else 0 end) as `崇明区`,
|
|
SUM(case when BUREAU_UNIT_NAME like '%静安%' or ADDR like '%静安%' then 1 else 0 end) as `静安区`,
|
|
SUM(case when BUREAU_UNIT_NAME like '%松江%' or ADDR like '%松江%' then 1 else 0 end) as `松江区`,
|
|
SUM(case when BUREAU_UNIT_NAME like '%长宁%' or ADDR like '%长宁%' then 1 else 0 end) as `长宁区`,
|
|
SUM(case when BUREAU_UNIT_NAME like '%闵行%' or ADDR like '%闵行%' then 1 else 0 end) as `闵行区`,
|
|
SUM(case when BUREAU_UNIT_NAME like '%黄浦%' or ADDR like '%黄浦%' then 1 else 0 end) as `黄浦区`,
|
|
SUM(case when BUREAU_UNIT_NAME like '%金山%' or ADDR like '%金山%' then 1 else 0 end) as `金山区`,
|
|
SUM(case when BUREAU_UNIT_NAME like '%杨浦%' or ADDR like '%杨浦%' then 1 else 0 end) as `杨浦区`,
|
|
SUM(case when BUREAU_UNIT_NAME like '%嘉定%' or ADDR like '%嘉定%' then 1 else 0 end) as `嘉定区`,
|
|
SUM(case when BUREAU_UNIT_NAME like '%普陀%' or ADDR like '%普陀%' then 1 else 0 end) as `普陀区`,
|
|
SUM(case when BUREAU_UNIT_NAME like '%宝山%' or ADDR like '%宝山%' then 1 else 0 end) as `宝山区`,
|
|
SUM(case when BUREAU_UNIT_NAME like '%浦东新%' or BUREAU_UNIT_NAME like '%自由贸易%' then 1 else 0 end) as `浦东新区`,
|
|
SUM(case when BUREAU_UNIT_NAME like '%奉贤%' or ADDR like '%奉贤%' then 1 else 0 end) as `奉贤区`,
|
|
SUM(case when BUREAU_UNIT_NAME like '%青浦%' or ADDR like '%青浦%' then 1 else 0 end) as `青浦区`,
|
|
SUM(case when BUREAU_UNIT_NAME like '%虹口%' or ADDR like '%虹口%' then 1 else 0 end) as `虹口区`
|