查询车辆信息及关联人员信息

SELECT c114 AS plate_no_value, c118 AS last_snap_time, c115 AS plate_color_value, c117 AS plate_url, c111 AS bkg_url, c112 AS cred_img_url, c56 AS device_code, c62 AS device_name, c36 AS person_type, c60 AS police_station_name, c61 AS property_company_phone_number, c40 AS human_id, c44 AS cert_num, c45 AS contact_phone, c70 AS community_code, c72 AS community_full_name, c74 AS region_name, c76 AS street_road_name, c77 AS communitee_name FROM (SELECT c114, c118, c115, c117, c111, c112, c56, c62, c36, c60, c61, c40, c44, c45, c70, c72, c74, c76, c77 FROM (SELECT c114, c115, c118, c117, c111, c112, c56, c60, c61, c62, c70, c72, c74, c76, c77, c36, c40, c44, c45 FROM (SELECT c82, c109, c114, c115, c118, c117, c111, c112, c56, c60, c61, c62, c70, c72, c74, c76, c77 FROM (SELECT c82, c109, c114, c115, c106, c118, c116, c107, c117, c111, c112 FROM (SELECT c80, c82, c90 FROM (SELECT * FROM (SELECT 'is_valid' AS 'c79','veh_plate_no' AS 'c80','veh_plate_type_code' AS 'c81','cert_num' AS 'c82','motor_veh_serial_no' AS 'c83','veh_state_code' AS 'c84','veh_plate_type_name' AS 'c85','first_registrate_date' AS 'c86' FROM (SELECT 'is_valid','veh_plate_no','veh_plate_type_code','cert_num','motor_veh_serial_no','veh_state_code','veh_plate_type_name','first_registrate_date' FROM 'dwd_vehicle_register_info') tempt) tempt WHERE (c84 NOT LIKE ('%B%') AND c84 NOT LIKE ('%E%')) AND (c79 IN (1))) t0 INNER JOIN (SELECT 'veh_plate_type_code' AS 'c87','veh_plate_type_name' AS 'c88','plate_color' AS 'c89','plate_color_value' AS 'c90','plate_type' AS 'c91','plate_type_value' AS 'c92' FROM (SELECT 'veh_plate_type_code','veh_plate_type_name','plate_color_value','plate_type','plate_type_value','plate_color' FROM 'dim_vehicle_code') tempt) t1 ON t0.c81 = t1.c87) t0 INNER JOIN (SELECT t1.c109,t1.c114,t1.c115,t1.c106,t1.c118,t1.c116,t1.c107,t1.c117,t1.c111,t1.c112 FROM (SELECT rowkey AS c109,plate_no_value AS c114,plate_color_value AS c115,camera_index_code AS c106,pass_time AS c118,plate_type_value AS c116,prtday AS c107,plate_url AS c117,bkg_url AS c111,cred_img_url AS c112 FROM (SELECT t1.rowkey, t1.plate_no_value, t1.plate_color_value, t1.camera_index_code, to_timestamp(cast(t1.pass_time/1000 as bigint)) AS pass_time, t1.plate_type_value, t1.prtday, cast(null as string) plate_url, cast(null as string) bkg_url, cast(null as string) cred_img_url from ods_vehicle_snap t1 where t1.plate_color_value!='other' and prtday >= 20220701 and prtday < 20220801 ) tempt) t1 join (select t2.c114,t2.c115,max(t2.c118) as c118 from (select rowkey as c109,plate_no_value as c114,plate_color_value as c115,camera_index_code as c106,pass_time as c118,plate_type_value as c116,prtday as c107,plate_url as c117,bkg_url as c111,cred_img_url as c112 from (select t1.rowkey, t1.plate_no_value, t1.plate_color_value, t1.camera_index_code, to_timestamp(cast(t1.pass_time/1000 as bigint)) as pass_time, t1.plate_type_value, t1.prtday, cast(null as string) plate_url, cast(null as string) bkg_url, cast(null as string) cred_img_url from ods_vehicle_snap t1 where t1.plate_color_value!='other' and prtday >= 20220701 and prtday < 20220801 ) tempt) t2 group by t2.c114,t2.c115 ) t3 on t1.c114=t3.c114 and t1.c115=t3.c115 and t1.c118=t3.c118) t1 on t0.c80 = t1.c114 and t0.c90 = t1.c115 ) t0 INNER JOIN (SELECT * FROM (SELECT 'device_code' AS 'c56','community_address' AS 'c57','is_valid' AS 'c58','police_station_code' AS 'c59','police_station_name' AS 'c60','property_company_phone_number' AS 'c61','device_name' AS 'c62','device_alias' AS 'c63','device_type_code' AS 'c64','device_type_code_format' AS 'c65','longitude' AS 'c66','latitude' AS 'c67','update_time' AS 'c68','primary_key' AS 'c69','community_code' AS 'c70','community_brief_name' AS 'c71','community_full_name' AS 'c72','region_code' AS 'c73','region_name' AS 'c74','street_road_code' AS 'c75','street_road_name' AS 'c76','communitee_name' AS 'c77','communitee_code' AS 'c78' FROM 'dws_community_device_info') tempt WHERE c64 IN ('9') AND c58 IN (1)) t1 ON t0.c106 = t1.c56) t0 INNER JOIN (SELECT 'gender_code' AS 'c35','person_type' AS 'c36','common_cert_type_code' AS 'c37','temp_person_is_valid' AS 'c38','name' AS 'c39','human_id' AS 'c40','gender_name' AS 'c41','current_reside_address' AS 'c42','info_primary_key' AS 'c43','cert_num' AS 'c44','contact_phone' AS 'c45','permanent_person_is_valid' AS 'c46' FROM 'dws_person_base_info') t1 ON t0.c82 = t1.c44) tempt) tempt;