卡嘉项目中使用的mysql语句

1.模拟包发送数据开始

update device_info set device_info.REMARK='pressure' LIMIT 50000;

update device_info set device_info.REMARK='pressure' WHERE device_info.REMARK='1';

 

 2查看报的数据有多少设备是设备表中的

SELECT COUNT(1) from kajia_device_position_info kdpi INNER JOIN device_info di

ON kdpi.DEVICE_ID=di.DEVICE_ID WHERE kdpi.POS_TIME >'2017-12-27 19:00:00';

 

3查看是否30s发送一次

SELECT dph.vin,dph.POS_TIME,dph.CREATE_TIME,dph.RECIVED_TIME,dph.UPDATE_TIME from device_position_his1711 dph where vin='H087947' ORDER BY POS_TIME DESC;

 

4查看在某一秒内插入数据库的条数

SELECT COUNT(*),UPDATE_TIME

from kajia_device_position_info GROUP BY UPDATE_TIME ORDER BY UPDATE_TIME DESC ;

 

SELECT update_time,count(*)

from device_position_his1711 group by update_time having count(*)>100 ORDER BY UPDATE_TIME DESC;

 

SELECT vin ,count(*)

from device_position_his1711 group by vin ;

 

查看在某一秒内插入数据库的条数

SELECT update_time,count(*)

from kajia_device_position_info dpi group by update_time ;

 

查看网关接收时间与上报相差大于10分钟的

SELECT vin,POS_TIME,CREATE_TIME,RECIVED_TIME,UPDATE_TIME from device_position_his1711 dph

where TIME_TO_SEC(TIMEDIFF (dph.RECIVED_TIME,dph.POS_TIME))>600 and pos_time >'2017-11-22 00;00:00';

 

SELECT count(*) from device_position_his1711 dph

where TIME_TO_SEC(TIMEDIFF (dph.RECIVED_TIME,dph.POS_TIME))>600 and pos_time >'2017-11-22 19;10:00';

 

从数据库到网关的 大于

SELECT vin,POS_TIME,CREATE_TIME,RECIVED_TIME,UPDATE_TIME from device_position_his1711 dph

where TIME_TO_SEC(TIMEDIFF (dph.UPDATE_TIME,dph.RECIVED_TIME))>60 and pos_time >'2017-11-22 00;00:00';

 

查看某车上报的时间点

SELECT vin,POS_TIME,CREATE_TIME,RECIVED_TIME,UPDATE_TIME from device_position_his1711 dph

where SIM='18278009798' ORDER BY POS_TIME DESC;

 

查看有多少设备在上报过位置

SELECT count(*)from kajia_device_position_info

where pos_time >'2017-12-14 22:00:00';

 

查看到网关，到DSE超过1秒的数据量

SELECT count(*) from device_position_his1712 dph

where TIME_TO_SEC(TIMEDIFF (dph.RECIVED_TIME,dph.POS_TIME))>1 and pos_time >'2017-12-15 11:00:00';

 

SELECT count(*) from device_position_his1712 dph

where TIME_TO_SEC(TIMEDIFF (dph.CREATE_TIME,dph.RECIVED_TIME))>1 and pos_time >'2017-12-15 11:00:00';

 

SELECT count(*) from device_position_his1712 dph

where pos_time >'2017-12-15 11:00:00';

 

一小时查看

SELECT count(*) from device_position_his1712 dph

where TIME_TO_SEC(TIMEDIFF (dph.CREATE_TIME,dph.RECIVED_TIME))>1 and  pos_time >'2017-12-21 07:50:00'

and pos_time <'2017-12-21 08:50:00';

 

每隔一小时小时查看数量

 

SELECT count(*) from device_position_his1712 dph

where TIME_TO_SEC(TIMEDIFF (dph.RECIVED_TIME,dph.POS_TIME))>1 and pos_time BETWEEN '2017-12-04 19:50:00' and '2017-12-04 20:10:00';

 

SELECT count(*) from device_position_his1712 dph

where TIME_TO_SEC(TIMEDIFF (dph.UPDATE_TIME,dph.RECIVED_TIME))>1 and pos_time BETWEEN '2017-12-04 19:50:00' and '2017-12-04 20:10:00';

 

SELECT count(*) from device_position_his1712 dph

where pos_time BETWEEN '2017-12-04 19:50:00' and '2017-12-04 20:10:00';

 

查出不同设备发送过的报警数多少条

SELECT COUNT(DISTINCT DEVICE_ID) from kajia_vehicle_alarm_msg where pos_time >= "2017-12-04 00:00:00";

 

select oa.lpno,oa.user_id,oa.order_time,oa.order_no,ui.push_id,datediff(oa.order_time,now())

days,oa.remind_count from study_base.kajia_order_apply oa

inner join

study_base.user_info ui on oa.user_id=ui.user_id where

ui.status=1 and

oa.status=2 and ifnull(ui.push_id,'')!=''

and datediff(oa.order_time,now())<=5 and datediff(oa.order_time,now())>=0

 

and ifnull(oa.remind_count,0)<2