人员活动状态查询 - 人员活动正常、疑似离开、长期未出现

SELECT c100 AS human_id, c241 AS last_snap_time, c99 AS name, c242 AS last_snap_face_url, c104 AS cert_num, c243 AS last_snap_bkg_url, c96 AS person_type, c101 AS gender_name, c105 AS contact_phone, c73 AS is_valid, c202 AS snap_day, c197 AS snap_times, c231 AS last_snap_property_company_phone, c235 AS last_snap_community_code, c236 AS last_snap_community_name, c203 AS face_times, c187 AS vehicle_times, c205 AS reg_community_code, c207 AS reg_community_name, c219 AS reg_community_address, c211 AS reg_com_street_road_name, c213 AS reg_com_communitee_name, c215 AS reg_com_police_station_name, c216 AS reg_com_property_company_phone, c183 AS control_state, c14 AS key_person_category_code, c15 AS key_person_category_name, CURRENT_DATE() AS calc_date FROM ( SELECT c100, c241, c99, c242, c104, c243, c96, c101, c105, c73, c202, c197, c231, c235, c236, c203, c187, c205, c207, c219, c211, c213, c215, c216, c183, c14, c15 FROM ( SELECT c96, c99, c100, c101, c104, c105, c14, c15, c205, c207, c211, c213, c215, c216, c219, c73, c185, c187, c192, c197, c202, c203, c241, c242, c243, c245, c231, c235, c236 FROM ( SELECT c96, c99, c100, c101, c104, c105, c14, c15, c205, c207, c211, c213, c215, c216, c219, c73, c185, c187, c192, c197, c202, c203 FROM (SELECT NULL) t0 LEFT JOIN ( SELECT * FROM ( SELECT last_snap_day AS c185, community_full_name AS c186, vehicle_times AS c187, region_name AS c188, gender_name AS c189, street_road_name AS c190, communitee_name AS c191, cert_num AS c192, contact_phone AS c193, police_station_name AS c194, person_type AS c195, property_company_phone_number AS c196, snap_times AS c197, calc_date AS c198, name AS c199, community_code AS c200, human_id AS c201, snap_day AS c202, face_times AS c203 FROM dws_com_snap_statistics_middle_last30day ) tempt WHERE c198 IN ('2022-08-05') ) t1 ON t0.c104 = t1.c192 AND t0.c205 = t1.c200 ) t0 LEFT JOIN ( SELECT last_snap_time AS c241, face_url AS c242, bkg_url AS c243, cred_img_url AS c244, human_id AS c245, property_company_phone_number AS c231, cert_num AS c233, device_code AS c227, device_name AS c228, person_type AS c229, contact_phone AS c234, community_code AS c235, community_full_name AS c236, region_name AS c237, calc_date AS c240, street_road_name AS c238, communitee_name AS c239, police_station_name AS c230 FROM dws_com_last_snap_statistics ) t1 ON t0.c100 = t1.c245 ) tempt ) tempt WHERE CASE WHEN c192 IS NOT NULL AND c185 >= CURRENT_DATE() - INTERVAL 15 DAY THEN '人员活动正常' WHEN c245 IS NOT NULL AND c241 >= CURRENT_DATE() - INTERVAL 30 DAY AND c205 <> c235 THEN '人员疑似离开原居住地' WHEN c241 < CURRENT_DATE() - INTERVAL 30 DAY OR c241 IS NULL THEN '人员长期未出现' END IS NOT NULL;