sxgps51876373GPS@gpsdb


select t.simid,t.vehiclenuber,t.licenseplate,t.gpsid,t.pingtai,t.registerdate,t.endtime,t.enddate,t.rowid from VEHICLEINFO t where t.endtime<'2017-12-31';
select t.simid,t.vehiclenuber,t.licenseplate,t.gpsid,t.pingtai,t.registerdate,t.endtime,t.enddate,t.rowid from VEHICLEINFO t where t.endtime>'2017-12-31' and t.endtime<'2018-12-31' ;

update VEHICLEINFO t set t.enddate='2018-12-31' where t.endtime>'2017-12-31' and t.endtime<'2018-12-31' ;

select t.*, t.rowid from KLSTATUS201906 t;
select t.*, t.rowid from KLSTATUS201906 t where t.simid='14440958477'


select t.simid,t.vehiclenuber,t.licenseplate,t.gpsid,t.pingtai,t.motorcadeid,t.registerdate,t.remark,t.rowid from VEHICLEINFO t where t.pingtai = 'ZZTXD'
select t.simid,t.vehiclenuber,t.licenseplate,t.gpsid,t.pingtai,t.motorcadeid,t.registerdate,t.remark,t.rowid from VEHICLEINFO t where t.pingtai='SHSX';



޸TOTALֿ⣬ͻʱη䣻01ͻ
select t.*, t.rowid from WAREHOUSEINFO t  where t.warehouse = 'H502';

ѯ˾˺ţ
select t.*, t.rowid from userinfo t where t.companyname like '%%';

ѯţ
select t.*, t.rowid from VEHICLEINFO t where t.vehiclenuber like '%%';
select t.simid,t.vehiclenuber,t.licenseplate,t.simid,t.registerdate,t.remark,t.endtime,t.startdate,t.expense,t.finallytime,t.rowid from VEHICLEINFO t where t.vehiclenuber like 'CML%';

update VEHICLEINFO t set t.expense='480' where t.vehiclenuber like '%';


select t.*, t.rowid from VEHICLEINFO t where   t.gpsid='9161018340';

ѯƽ̨зΪյĳ
select t.simid,t.vehiclenuber,t.licenseplate,t.gpsid,t.registerdate,t.remark,t.endtime,t.startdate,t.expense,t.finallytime,t.rowid from vehicleinfo t where t.expense is null and t.pingtai in ('SHSX','TOTAL','TOTALFUEL')

COPYdownloadsmsȫݵdownloadsms1У
insert into downloadsms1 select * from downloadsms;
commit;

յδûĶϢ  
ձ
TRUNCATE TABLE DOWNLOADSMS;   
в
delete from DOWNLOADSMS where ( TIME <'2017-04');

ѯ
select t.*, t.rowid from VEHICLEINFO t where t.licenseplate like '%';

޸ָ복ŵĳѣ
update VEHICLEINFO t set t.expense='240' where t.licenseplate like '%';
update VEHICLEINFO t set t.expense='720' where t.vehiclenuber like '%';

޸ָ˺г  =====
update  VEHICLEINFO   set expense='240' where simid in ( select t.simid from  VEHICLEINFO t, USERVEHICLE V where T.SIMID =V.SIMID and v.userid='shzx1'); 

ѯshzx1˺г
select t.*, t.rowid from VEHICLEINFO t ,USERVEHICLE V where T.SIMID =V.SIMID and v.userid='hmfy1'; 

ѯshzx1˺г(Ϊ)
select t.simid,t.vehiclenuber,t.licenseplate,t.gpsid,t.registerdate,t.remark,t.endtime,t.startdate,t.expense,t.finallytime,t.rowid from VEHICLEINFO t ,USERVEHICLE V where T.SIMID =V.SIMID and v.userid='xsh1';


豸˺shzx1гĽֹڸΪ'2018-07-26'
update  VEHICLEINFO   set endtime='2018-04-21' where simid in ( select t.simid from  VEHICLEINFO t, USERVEHICLE V where T.SIMID =V.SIMID and v.userid='hmfy1')

ͨƽ̨ŲѯϸϢ
select t.simid,t.vehiclenuber,t.licenseplate,t.gpsid,t.registerdate,t.endtime,t.startdate,t.finallytime,t.rowid from vehicleinfo t where t.pingtai='XXCWD'


ѯûʹõ
select distinct userid,companyname ,max(operatingtime) from (select t.userid,t.companyname, v.operatingtime from USERINFO t LEFT JOIN LOGOPERATING201708 v on t.userid=v.username where t.pingtai in ('SHSX','TOTAL')
union
select distinct userid,t.companyname,  v.operatingtime from USERINFO t LEFT JOIN LOGOPERATING201707 v on t.userid=v.username where t.pingtai in ('SHSX','TOTAL')
union
 select distinct userid,t.companyname,  v.operatingtime from USERINFO t LEFT JOIN LOGOPERATING201706 v on t.userid=v.username where t.pingtai in ('SHSX','TOTAL'))  group by userid,companyname

select t.*, t.rowid from VEHICLEINFO t where pingtai='zyyj';

ͨƽ̨ŹѯϢ״̬ݣɾ״̬״̬ʱ䡣
delete from  latelygpsinfo a  where  a.simid in( select t.simid from VEHICLEINFO t  
 join latelygpsinfo l on t.simid=l.simid  where t.pingtai='TOTAL' )

Ʊṹݣ
create table VEHICLEINFO_new as select * from VEHICLEINFO;
create table userinfo_new as select * from userinfo;
create table userVEHICLE_new as select * from userVEHICLE;

select t.*, t.rowid from VEHICLEINFO t where pingtai='ZYYJ';
select t.*,t.rowid from userinfo t where pingtai='ZYYJ'
select t.*,t.rowid from uservehicle t where pingtai='ZYYJ'

delete from VEHICLEINFO t where pingtai='ZYYJ';
delete from USERINFO t where pingtai='ZYYJ';
delete from USERVEHICLE t where pingtai='ZYYJ';

imp sxgps/sxgps51876373GPS@gpsdb;

delete from inceptsms where sms like '%˾Ҫƣͼʻ%';
delete from inceptsms where time<'2019-06-01 00:00:00';
delete from vehicledistance where begintime<'2019-06-01 00:00:00';
delete from contactalarm where begintime<'2019-06-01 00:00:00';
delete from parkinglog where begintime<'2019-06-01 00:00:00';
delete from linealarmreprot where begintime<'2019-06-01 00:00:00';
delete from speedalarm where timelog<'2019-06-01 00:00:00';
delete from  VEHICLEWORKTIME where begintime<'2019-06-01 00:00:00';
TRUNCATE TABLE ALARMINFOWORKTIME;
TRUNCATE TABLE USERAPPLOG;
TRUNCATE TABLE DOWNLOADSMS;
commit;


ͨеָûϢ
exp userid=sxgps/sxgps51876373GPS@SXDBTEMP_222.73.54.14 tables=(vehicleinfo,LATELYGPSINFO) file=d:\daochu20191118.dmp  

ͨеָdmpļľ
imp gygps/sx51876373@sxgps  file=d:\error.dmp tables=(gpsdata20171208)     