取保候审人员状态监控数据
SELECT c156 AS calc_date,
c158 AS bail_pending_trial_control_state,
c159 AS current_suspect_live_community_code,
c160 AS current_suspect_live_community_name,
c147 AS name,
c152 AS cert_num,
c148 AS human_id,
c149 AS gender_name,
c153 AS contact_phone,
c135 AS key_person_category_name,
c144 AS person_type,
c30 AS reg_community_code,
c93 AS reg_community_name,
c105 AS reg_community_address,
c179 AS last_snap_time,
c180 AS last_snap_face_url,
c181 AS last_snap_bkg_url,
c173 AS last_snap_community_code,
c174 AS last_snap_community_name,
c131 AS occurrence_days
FROM (SELECT c156,
c158,
c159,
c160,
c147,
c152,
c148,
c149,
c153,
c135,
c144,
c30,
c93,
c105,
c179,
c180,
c181,
c173,
c174,
c131
FROM (SELECT c135,
c30,
c93,
c105,
c110,
c179,
c180,
c181,
c173,
c174,
c131,
c156,
c158,
c159,
c160,
c144,
c147,
c148,
c149,
c152,
c153
FROM (SELECT c132,
c135,
c142,
c30,
c93,
c105,
c110,
c111,
c117,
c125,
c179,
c180,
c181,
c173,
c174,
COALESCE(DATEDIFF(CURRENT_DATE, c179), 30) AS c131,
CURRENT_DATE AS c156,
CASE
WHEN (c117 IS NOT NULL AND c110 >= CURRENT_DATE - INTERVAL 15 DAY AND c30 = c125) THEN '人员活动正常'
WHEN c179 >= CURRENT_DATE - INTERVAL 30 DAY AND c30 <> c173 THEN '取保候审人员疑似离开原居住地'
WHEN c179 < CURRENT_DATE - INTERVAL 30 DAY OR c179 IS NULL THEN '取保候审人员长期未出现'
END AS c158,
CASE
WHEN c117 IS NOT NULL AND c110 >= CURRENT_DATE - INTERVAL 30 DAY AND c30 <> c125 THEN c125
ELSE NULL
END AS c159,
CASE
WHEN c117 IS NOT NULL AND c110 >= CURRENT_DATE - INTERVAL 30 DAY AND c30 <> c125 THEN c111
ELSE NULL
END AS c160
FROM (SELECT c132,
c135,
c142,
c30,
c93,
c105,
c110,
c111,
c117,
c125,
c179,
c180,
c181,
c173,
c174
FROM (NULL) t0
LEFT JOIN (SELECT last_snap_time AS c179,
face_url AS c180,
bkg_url AS c181,
cred_img_url AS c182,
human_id AS c183,
property_company_phone_number AS c169,
cert_num AS c171,
device_code AS c165,
device_name AS c166,
person_type AS c167,
contact_phone AS c172,
community_code AS c173,
community_full_name AS c174,
region_name AS c175,
calc_date AS c178,
street_road_name AS c176,
communitee_name AS c177,
police_station_name AS c168
FROM dws_com_last_snap_statistics
) t1 ON t0.c142 = t1.c171
) tempt
) t0
INNER JOIN (SELECT gender_code AS c143,
person_type AS c144,
common_cert_type_code AS c145,
temp_person_is_valid AS c146,
name AS c147,
human_id AS c148,
gender_name AS c149,
current_reside_address AS c150,
info_primary_key AS c151,
cert_num AS c152,
contact_phone AS c153,
permanent_person_is_valid AS c154
FROM dws_person_base_info
) t1 ON t0.c142 = t1.c152
) tempt
) tempt
) tempt
原文地址: https://www.cveoy.top/t/topic/oNlA 著作权归作者所有。请勿转载和采集!