统计sql整理

项目统计sql记录：

获取不同诚信等级的企业数

1：首先获取某个网格区域，某个企业类型所有的活动的企业数

2：获取上一年的惩罚企业数（排除重复的企业一年中多次惩罚记录，获取最新一条即可）

3：排除诚信评定中同一个企业在同一个评定级别中多条记录去最新的企业的评定等级

4：排除诚信评定中企业在上一年被惩罚的企业

SELECT ec.score_level,COUNT(ec.score_level) AS count

FROM g_enterprise_credit ec

INNER JOIN (

     SELECT enterprise_id,MAX(update_time) AS update_time,score_level FROM g_enterprise_credit

     GROUP BY

     enterprise_id

) m ON m.enterprise_id = ec.enterprise_id AND m.update_time = ec.update_time AND m.score_level =        ec.score_level

INNER JOIN g_enterprises AS en ON en.id = ec.enterprise_id

LEFT JOIN g_enterprise_type AS et ON et.id = ec.enterprise_type_id

LEFT JOIN g_system_netregion AS sn ON sn.id = en.net_range_id

WHERE

(

ec.is_tmp_storage = 0

AND ec.`status` = 2

AND ec.enterprise_type_id LIKE '0112%'

AND en.net_range_id IN (

'DD5FA7FA-80D4-CB29-C4D4-E0592EFBA8A8',

'E48D6B98-A834-E8FC-083E-809B006C6615'

)

AND en.is_active = 1

AND ec.enterprise_id NOT IN (

     SELECT e.id FROM  g_administrative_penalty ap

     INNER JOIN (

         SELECT MAX(penalty_time) AS penalty_time,enterprise_id FROM

         g_administrative_penalty GROUP BY enterprise_id

) m ON m.penalty_time = ap.penalty_time AND m.enterprise_id = ap.enterprise_id

INNER JOIN g_enterprises e ON e.id = ap.enterprise_id

INNER JOIN g_enterprise_type et ON et.id = ap.enterprise_type_id WHERE

e.is_active = 1

AND e.register_area_stan LIKE '321081%'

AND e.net_range_id IN (

'DD5FA7FA-80D4-CB29-C4D4-E0592EFBA8A8',

'E48D6B98-A834-E8FC-083E-809B006C6615'

)

AND ap.is_active = '1'

AND (

ap.penalty_time >= 1420041600

AND ap.penalty_time <= 1451491200

)

AND ap.enterprise_type_id LIKE '0112%'

AND e.id IN (

SELECT

a.id

FROM

g_enterprises a

LEFT JOIN g_area_stan c ON c.area_code = a.register_area_stan

LEFT JOIN g_system_netregion d ON d.id = a.net_range_id

LEFT JOIN g_re_enterprises_types et ON et.enterprise_id = a.id

WHERE

1 = 1

AND a.register_area_stan LIKE '321081%'

AND a.is_active = 1

AND net_range_id IN (

'DD5FA7FA-80D4-CB29-C4D4-E0592EFBA8A8',

'EDB7F03B-994B-741D-8050-023ED8ED511B')

AND enterprise_type_id LIKE '0112%'

GROUP BY

a.id

)

ORDER BY

ap.penalty_time DESC

)

)

GROUP BY

ec.score_level;