mysql数据库表关联查询sql语句

雷东

已于 2023-12-23 15:19:48 修改

阅读量456

点赞数 7

文章标签：数据库 mysql

于 2023-12-23 15:18:40 首次发布

本文链接：https://blog.csdn.net/leidong2009/article/details/135169354

版权

该篇文章详细记录了车辆冀A88PE的多项信息，包括公司、车牌号、设备ID、GPS位置、轮胎监控状态（低压、高压、高温等）、行驶里程、以及与之关联的GPS数据和车辆故障报警情况。

摘要由CSDN通过智能技术生成

SELECT
a.id ,
a.companyid ,
a.carno ,
'' as carNoConcat,
a.receiveid ,
(select t.carno from car_info t where id=carhand.handcarid and serialnum=1) handcarno,
a.repeaterid ,
a.deviceid ,
(if(CONV( a.deviceid,10,16)=0,'',CONV( a.deviceid,10,16))) as deviceId16 ,
a.imgurl ,
concat('https://wltx-bucket.oss-cn-hangzhou.aliyuncs.com/wltx/ntire/img/carimg/vehicletype/',a.vehicletype,'.jpg') as vehicletypeimg,
( REPLACE(REPLACE(CONCAT_WS(',' ,
                               (if(t1.lowpress_status=1,'低压报警','正常') ),
                               (if(t1.cold_lowpress_status=1,'冷态低压报警','正常') ),
                               (if(t1.highpress_status=1,'高压报警','正常') ),
                               (if(t1.hightemperature_status=1,'高温报警','正常') ),
(if(t1.serioushightemperature_status=1,'严重高温报警','正常') ),
                               (if(t1.batteryvoltage_status=1,'电池电压报警','正常')),
                               (if(t1.nosignal_status=1,'无信号报警','正常') ),
                               (if(t3.nosignal_all_status=1,'整车失联','正常') ),
                               (if(t3.gps_status=1,'GPS无数据','正常') ),
                                               (if(t3.lacktireall_status=1,'缺轮','正常') )
           ),',正常',''),'正常,','')
) as monitorstatus ,
a.monitordate ,
a.totalmileage ,
a.totalduration ,
a.birthmileage ,
a.totalworkduration ,
a.readmileage ,
a.readmileage_date ,
a.readduration ,
a.readduration_date ,
a.remark ,
a.insidecarno ,
a.havegps ,
a.gps_longitude ,
a.gps_latitude ,
a.gps_longitude_gd ,
a.gps_latitude_gd ,
a.gps_speed ,
a.gps_date ,
(select t.gps_date from car_info t where id=carhand.handcarid and serialnum=1) as handGpsDate,
a.c1_date ,
a.gps_fencestatus ,
a.province ,
a.city ,
a.county ,
a.weather ,
a.temperature ,
a.humidity ,
a.windpower ,
a.tranport ,
a.tranportall ,
a.vehicle ,
a.vehicleall ,
a.brand ,
a.specifications ,
a.truckcategory ,
a.simcard ,
a.simstartdate ,
a.simenddate ,
a.tirepositionchanged ,
a.synchparamchanged ,
a.isflash ,
a.isdomain ,
a.isfloataxis ,
a.cartype ,
a.handcartype ,
a.vehicletype ,
(select t.vehicletype from car_info t where t.carno=a.handcarno LIMIT 1)   as    handvehicletype   ,
(select t.deviceid from car_info t where t.carno=a.handcarno LIMIT 1)   as    handDeviceId   ,
(select (if(CONV( t.deviceid,10,16)=0,'',CONV( t.deviceid,10,16))) from car_info t where t.carno=a.handcarno LIMIT 1)   as    handDeviceId16   ,
a.icc_id ,
a.icc_id_date ,
a.loadweight ,
a.workareaid ,
a.minearea ,
a.drivername ,
a.drivermobile ,
a.openid ,
a.flag ,
(select if(count(*)>0,1,0) from car_hand t1 where t1.handcarid=a.id) as ishand ,
a.isvalid ,
a.createuserid ,
a.createtime ,
a.updatetime ,
a.issync ,
a.issyncdata ,
a.circuit ,
a.modelnumber ,
a.factorynumber ,
a.wheelposdowntime ,
(SELECT t.totalmileage from car_info t where t.carno=a.handcarno) as totalmileage_hand,
b.companyname
,( SELECT count( 1 ) FROM tire_info WHERE carid = a.id ) AS tirecount
,(select model from dev_info tt1 where tt1.deviceID=a.deviceid limit 1) as model
,(select model from dev_info tt1 where tt1.deviceID=(select t.deviceid from car_info t where t.id=carhand.handcarid and t.isusered=1 LIMIT 1 ) limit 1) as handModel
,(select batteryLevel from dev_info tt1 where tt1.deviceID=a.deviceid AND a.flag=0 limit 1) as batteryLevel
,(select DATE_FORMAT(batteryLevelupdateTime,'%Y-%m-%d %H:%i:%s') from dev_info tt1 where tt1.deviceID=a.deviceid AND a.flag=0 limit 1) as batteryLevelupdateTime
                               ,(select batteryLevel from dev_info tt1 where tt1.deviceID=(select t.deviceid from car_info t WHERE ((a.flag=0 and t.id=carhand.handcarid) OR (a.flag=1 and t.id=a.id)) and t.isusered=1 LIMIT 1 ) limit 1) as handBatteryLevel
,(select DATE_FORMAT(batteryLevelupdateTime,'%Y-%m-%d %H:%i:%s') from dev_info tt1 where tt1.deviceID=(select t.deviceid from car_info t where ((a.flag=0 and t.id=carhand.handcarid) OR (a.flag=1 and t.id=a.id)) and t.isusered=1 LIMIT 1 ) limit 1) as handbatteryLevelupdateTime
                                       ,(select extPowerStatus from dev_info tt1 where tt1.deviceID=a.deviceid AND a.flag=0 limit 1) as extPowerStatus
,(select DATE_FORMAT(extPowerStatusUpdateTime,'%Y-%m-%d %H:%i:%s') from dev_info tt1 where tt1.deviceID=a.deviceid AND a.flag=0 limit 1) as extPowerStatusUpdateTime
                               ,(select extPowerStatus from dev_info tt1 where tt1.deviceID=(select t.deviceid from car_info t WHERE ((a.flag=0 and t.id=carhand.handcarid) OR (a.flag=1 and t.id=a.id)) and t.isusered=1 LIMIT 1 ) limit 1) as handextPowerStatus
,(select DATE_FORMAT(extPowerStatusUpdateTime,'%Y-%m-%d %H:%i:%s') from dev_info tt1 where tt1.deviceID=(select t.deviceid from car_info t where ((a.flag=0 and t.id=carhand.handcarid) OR (a.flag=1 and t.id=a.id)) and t.isusered=1 LIMIT 1 ) limit 1) as handextPowerStatusUpdateTime
                                       FROM
   car_info a
left join ( select
                                           if(sum(tim.lowPressure)>0,1,0) as lowpress_status
                                           ,if(sum(tim.coolLowPressure)>0,1,0) as cold_lowpress_status
                                           ,if(sum(tim.highPressure)>0,1,0) as highpress_status
                                           ,if(sum(tim.highTemperature)>0,1,0)as hightemperature_status

                                           ,if(sum(tim.superHighTemperature)>0,1,0) as serioushightemperature_status
                                           ,if(sum(tim.batteryVoltage)>0,1,0) as batteryvoltage_status
                                           ,if(sum(tim.noSignal)>0,1,0) as nosignal_status
                                           ,ti.carid
                                   from tire_info_monitorstatus tim
                                   left join tire_info ti on ti.id=tim.tire_id
where tim.tire_id is not null
                                   GROUP BY ti.carid
                                       ) t1 on t1.carid=a.id
                           left join (
                                       select
                                           if(sum(cis.nosignal_all_status)>0,1,0) as nosignal_all_status
                                           , if(sum(cis.gps_status)>0,1,0) as gps_status
                                           , if(sum(cis.lacktireall_status)>0,1,0) as lacktireall_status
                                           ,cis.carid
                                       from car_info_status cis
                                       where cis.carid is not null
                                       GROUP BY cis.carid
                                   ) t3 on t3.carid=a.id
                           LEFT JOIN car_hand carhand on carhand.carid=a.id
   LEFT JOIN car_company b ON a.companyid = b.companyid
WHERE
   a.carno ='冀A88PE'