1.更改年份：
update ezcrm_jq_pt t
set t.alarmtime = add_months(t.alarmtime,-12)
select to_char(t.alarmtime,'YYYY-MM-DD') from ezcrm_jq_ptt t
2.更新字段部分值：
update cartport1 t set t.hphm=replace(t.hphm,'苏','京');
update EZCRM_AJ t set t.afdz=replace( t.afdz,'阳','湖南省') where t.afdz like '%阳%'
3.截取字符串前几位替换：
(1)追加在前面
update EZCRM_CBA_YSXX t
set t.xgdwmc = '联合国'||substr(t.xgdwmc,1,2)
(2)追加在后面
update EZCRM_CBA_YSXX t
set t.xgdwmc = substr(t.xgdwmc,1,2)||'联合国'
4.截取字符串后几位替换：
(1)追加在后面
update EZCRM_CBA_YSXX t
set t.xgdwmc = substr(t.xgdwmc,length(t.xgdwmc)-2)||'联合国'
(2)追加在前面
update EZCRM_CBA_YSXX t
set t.xgdwmc = '联合国'||substr(t.xgdwmc,length(t.xgdwmc)-2)
5.删除某用户下所有表及约束
drop user FZFX cascade;
6.查询oracle数据表最后一行
-------------------------------------------------------------------------------------------
select b.dz dz,
b.mc mc,
b.bh bh,
b.x x,
b.y y,
b.starttime starttime,
b.endtime endtime,
b.bj bj
from (select t.lgbh bh,
t.lgmc mc,
t.dz dz,
0 bj,
t.location.SDO_POINT.X x,
t.location.SDO_POINT.Y y,
to_char(s.rzsj, 'YYYY-MM-DD HH24-MI-SS') starttime,
to_char(s.tfsj, 'YYYY-MM-DD HH24-MI-SS') endtime
from ezcrm_hotel_log s, ezcrm_hotel t
where s.lgbh = t.lgbh
and s.sfzh = '320401198908153410'
and to_char(s.rzsj, 'YYYY-MM-DD HH24-MI-SS') between
'2011-01-01 00:00:00' and '2013-08-12 00:00:00'
union
select t1.wbbh bh,
t1.wbmc mc,
t1.dz dz,
1 bj,
t1.location.SDO_POINT.X x,
t1.location.SDO_POINT.Y y,
to_char(s1.sxsj, 'YYYY-MM-DD HH24-MI-SS') starttime,
to_char(s1.xxsj, 'YYYY-MM-DD HH24-MI-SS') endtime
from ezcrm_wb_log s1, ezcrm_wb t1
where s1.wbbh = t1.wbbh
and s1.sfzh = '320401198908153410'
and to_char(s1.sxsj, 'YYYY-MM-DD HH24-MI-SS') between
'2011-01-01 00:00:00' and '2013-08-12 00:00:00') b
where 1 = 1
order by b.starttime
------------------------------------------------------------------------
select b.BH,
b.DM,
b.MC,
b.SJ,
b.DZ,
b.GDM,
b.MS,
b.X X,
b.Y Y
from (select distinct (t6.AJBH) BH,
t6.AJLBDM DM,
t6.AJMC MC,
to_char(t6.FASJ, 'YYYY-MM-DD') SJ,
t6.AFDZ DZ,
t6.ZZJGDM GDM,
t6.AJMS MS,
t6.location.SDO_POINT.x || '' X,
t6.location.SDO_POINT.y || '' Y
from EZCRM_AJ t6, EZCRM_HOTEL t3
where SDO_WITHIN_DISTANCE(t6.LOCATION,
t3.LOCATION,
'DISTANCE=1000 UNIT=M') = 'TRUE'
and t3.LGBH IN (3204022047)
and t6.fasj BETWEEN TO_DATE('20110101000000', 'YYYYMMDD HH24MISS') AND
TO_DATE('20130812000000', 'YYYYMMDD HH24MISS')
union
select distinct (t6.AJBH) BH,
t6.AJLBDM DM,
t6.AJMC MC,
to_char(t6.FASJ, 'YYYY-MM-DD') SJ,
t6.AFDZ DZ,
t6.ZZJGDM GDM,
t6.AJMS MS,
t6.location.SDO_POINT.x || '' X,
t6.location.SDO_POINT.y || '' Y
from EZCRM_AJ t6, EZCRM_WB t4
where SDO_WITHIN_DISTANCE(t6.LOCATION,
t4.LOCATION,
'DISTANCE=1000 UNIT=M') = 'TRUE'
and t4.WBBH IN (3204020064,3204050245)
and t6.fasj BETWEEN
TO_DATE('20110101000000', 'YYYYMMDD HH24MISS') AND
TO_DATE('20130812000000', 'YYYYMMDD HH24MISS')) b
where 1 = 1
----------------------------------------------------------------------------
select t1.wbbh bh,
t1.wbmc mc,
t1.dz dz,
1 bj,
t1.location.SDO_POINT.X x,
t1.location.SDO_POINT.Y y
from ezcrm_wb t1
where SDO_WITHIN_DISTANCE(t1.location, SDO_GEOMETRY(2001,
8307,
SDO_POINT_TYPE(119.988112830394,
31.9301867501029,
NULL),
NULL,
NULL),
'DISTANCE=1000 UNIT=M') = 'TRUE'
UNION
SELECT t.lgbh bh,
t.lgmc mc,
t.dz dz,
0 bj,
t.location.SDO_POINT.X x,
t.location.SDO_POINT.Y y
from ezcrm_hotel t
where SDO_WITHIN_DISTANCE(t.location, SDO_GEOMETRY(2001,
8307,
SDO_POINT_TYPE(119.988112830394,
31.9301867501029,
NULL),
NULL,
NULL),
'DISTANCE=1000 UNIT=M') = 'TRUE'
海查系统SQL汇总：
select *
from CARTPORT1 t
where t.jgsj between to_date( '2017-10-08 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
and to_date( '2017-10-20 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
and t.sbmc='沪蓉高速青洋北路段(东向西)'and (t.hphm ='苏A68197'or t.hphm ='苏D99295') order by t.jgsj;
select *
from CARTPORT1 t
where t.jgsj between to_date( '2017-10-15 08:00:00', 'yyyy-MM-dd hh24:mi:ss')
and to_date( '2017-10-15 08:10:00', 'yyyy-MM-dd hh24:mi:ss')
and(t.sbmc='沪蓉高速青洋北路段(东向西)'or t.sbmc='沪蓉高速青洋北路段(南向北)') order by t.jgsj;
查找指定车辆在指定时间段内的基本轨迹数据
select count(*)
from ga_gps_history_2011_01_01 t
where to_char(t.time, 'yyyy-MM-dd hh24:mm:ss') between
'2012-03-01 00:00;00' and '2012-03-01 00:00;00'
and t.gpsid = '10000';
select count(*)
from ga_gps_history_2011_01_02 t
where to_char(t.time, 'yyyy-MM-dd hh24:mm:ss') between
'2012-03-03 10:00;00' and '2012-03-03 16:00;00'
and t.gpsid in ('10001','10000','10003');
查找在指定时间、指定区域内所有车辆的第一次和最后一次基本位置信息
select count(*)
from ga_gps_history_2011_01_02 t
where (to_char(t.time, 'yyyy-MM-dd hh24:mm:ss') between
'2012-03-03 10:00;00' and '2012-03-03 12:00;00')
and ((t.x between 116.37854 and 116.44274) and
(t.y between 39.89843 and 39.96801));
查找指定车辆在指定时间，指定区域内的第一次和最后一次的基本信息
select count(*)
from ga_gps_history_2011_01_02 t
where (to_char(t.time, 'yyyy-MM-dd hh24:mm:ss') between
'2012-03-12 00:00;00' and '2012-03-18 00:00;00')
and ((t.x between 115.37646 and 117.50927) and (t.y between 39.43896 and 40.97802))
and t.gpsid in ('10001','10000','10003');
select *
from cartport1 t, cartport_geo g
where t.jgsj between
to_date('2019-07-25 08:00:00', 'yyyy-MM-dd hh24:mi:ss') and
to_date('2019-07-25 10:00:00', 'yyyy-MM-dd hh24:mi:ss')
and ((g.x between 118 and 118) and (g.y between 39.99 and 39.99))
and t.sbmc = g.c_id;
delete from ga_gps_history_2011_01_01 where rownum<=20
create table ga_gps_history_2011_01_01 as select * from ga_gps_history_2012_03_01 t where 1=2
犯罪空间分析BS系统SQL汇总：
select * from ezcrm_jq_pt t where t.alarmtime = to_date('2012-08-01 ','yyyy-MM-dd')
insert into ga_gps_history_2011_05 select * from ga_gps_history_2011_06_12 t
select * from ga_gps_history_2012_03_01 t where gpsid='5110016' and to_char(time,'yyyyMMddHH24miss')='20120301095955'
select 'TODAY' TYPE, t.stattypedm DMTYPE, sum(count) COUNT,g.ajlbmc
from po_jq_type_stat_day t , ezcrm_dic_jq_type g
where t.statsj >= to_date('2012/08/07', 'yyyy/mm/dd')
and t.statsj < to_date('2012/08/08', 'yyyy/mm/dd')
and t.stattypedm in ('011701',
'011702',
'011703',
'020208',
'0116',
'0119',
'011705',
'020210',
'011706',
'020209',
'011713',
'0118')
and t.stattypedm=g.ajlbdm
group by t.stattypedm,g.ajlbmc
select 'ALL' TYPE, sum(count) COUNT
from po_jq_type_stat_day t
where t.statsj >= to_date('2012/07/31', 'yyyy/mm/dd')
and t.statsj < to_date('2012/08/01', 'yyyy/mm/dd')
and length(t.stattypedm) = 2
select * from PO_JQ_TYPE_STAT_DAY t
where t.statsj between to_date( '2012-07-25 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
and to_date( '2012-07-25 23:59:59', 'yyyy-MM-dd hh24:mi:ss')
and t.stattypedm='011701' and t.fjdm='320411000000' order by t.statsj;
select * from PO_JQ_TYPE_STAT_MONTH t
where to_date( t.statsj,'yyyyMM') between
to_date('201201','yyyyMM')and to_date('201212','yyyyMM')
and t.stattypedm='02' and t.fjdm='320404000000' and t.pcsdm ='320404580000' order by t.statsj;
select 'LQYD' TYPE, sum(count) COUNT
from po_jq_type_stat_month t
where to_date(t.statsj, 'yyyymm') >= to_date('201108', 'yyyymm')
and to_date(t.statsj, 'yyyymm') <= to_date('201108', 'yyyymm')
and t.stattypedm in ('0116', '0119')
select t.fjdm zzjgdm, sum(count) value
from po_jq_type_stat_day t
where t.fjdm in (320412000000,
320411000000,
320404000000,
320402000000,
320405000000)
AND STATSJ BETWEEN TO_DATE('2012/08/21', 'yyyy/mm/dd') AND
TO_DATE('2012/09/20', 'yyyy/mm/dd')
group by t.fjdm
select sum(count) num, substr(statsj, 5, 6) lastmon
from PO_JQ_TYPE_STAT_MONTH t
where t.statsj like '2010%'
and t.stattypedm in (011704,011713,070290,070490,070601)
group by t.statsj
order by t.statsj
select t.jqbh jqbh,
t.jqms jqms,
to_char(t.alarmtime, 'YYYY-MM-DD') afsj,
t.xldm xldm,
t.zldm zldm,
t.dldm dldm,
t.sspcsdm pcs,
t.ssgafjdm fj,
t.location.SDO_POINT.X x,
t.location.SDO_POINT.Y y
from ezcrm_jq_pt t
where 1 = 1
and to_char(t.alarmtime, 'YYYY-MM-DD') >= '2011-01-01'
and to_char(t.alarmtime, 'YYYY-MM-DD') <= '2011-11-10'
and dldm in ('01')
and zldm is null
select b.dz dz,
b.mc mc,
b.bh bh,
b.x x,
b.y y,
b.starttime starttime,
b.endtime endtime,
b.bj bj
from (select t.lgbh bh,
t.lgmc mc,
t.dz dz,
0 bj,
t.location.SDO_POINT.X x,
t.location.SDO_POINT.Y y,
to_char(s.rzsj, 'YYYY-MM-DD HH24-MI-SS') starttime,
to_char(s.tfsj, 'YYYY-MM-DD HH24-MI-SS') endtime
from ezcrm_hotel_log s, ezcrm_hotel t
where s.lgbh = t.lgbh
and s.sfzh = '510821198109218217'
and to_char(s.rzsj, 'YYYY-MM-DD HH24-MI-SS') between
'2000-01-01 00:00:00' and '2013-08-09 00:00:00'
union
select t1.wbbh bh,
t1.wbmc mc,
t1.dz dz,
1 bj,
t1.location.SDO_POINT.X x,
t1.location.SDO_POINT.Y y,
to_char(s1.sxsj, 'YYYY-MM-DD HH24-MI-SS') starttime,
to_char(s1.xxsj, 'YYYY-MM-DD HH24-MI-SS') endtime
from ezcrm_wb_log s1, ezcrm_wb t1
where s1.wbbh = t1.wbbh
and s1.sfzh = '510821198109218217'
and to_char(s1.sxsj, 'YYYY-MM-DD HH24-MI-SS') between
'2000-01-01 00:00:00' and '2013-08-09 00:00:00') b
where 1 = 1
order by b.starttime
SELECT T3.LGBH LGBH
FROM EZCRM_HOTEL_LOG T3
WHERE T3.RZSJ BETWEEN TO_DATE('20000101000000', 'YYYYMMDD HH24MISS') AND
TO_DATE('20130809000000', 'YYYYMMDD HH24MISS')
AND T3.SFZH = '510212195801020810'
GROUP BY LGBH
------------------
SELECT T2.WBBH WBBH
FROM EZCRM_WB_LOG T2
WHERE T2.SXSJ BETWEEN TO_DATE('20000101000000', 'YYYYMMDD HH24MISS') AND
TO_DATE('20130809000000', 'YYYYMMDD HH24MISS')
AND T2.SFZH = '510212195801020810'
GROUP BY WBBH
--------------------------
SELECT distinct (t6.AJBH),
t6.AJLBDM AJLB,
t6.AJMC,
to_char(t6.FASJ, 'YYYY-MM-DD') AFSJ,
t6.AFDZ,
t6.ZZJGDM,
t6.AJMS,
t6.location.SDO_POINT.x || '' X,
t6.location.SDO_POINT.y || '' Y
FROM EZCRM_AJ t6, EZCRM_HOTEL t4
WHERE SDO_WITHIN_DISTANCE(t6.LOCATION,
t4.LOCATION,
'DISTANCE=1000 UNIT=M') = 'TRUE'
and t4.LGBH IN
(3204041916, 3204810239, 3204042197, 3204810008, 3204041823)
and t6.fasj BETWEEN TO_DATE('20000101000000', 'YYYYMMDD HH24MISS') AND
TO_DATE('20130809000000', 'YYYYMMDD HH24MISS')
---------------------------
SELECT distinct (t6.AJBH),
t6.AJLBDM AJLB,
t6.AJMC,
to_char(t6.FASJ, 'YYYY-MM-DD') AFSJ,
t6.AFDZ,
t6.ZZJGDM,
t6.AJMS,
t6.location.SDO_POINT.x || '' X,
t6.location.SDO_POINT.y || '' Y
FROM EZCRM_AJ t6, EZCRM_WB t4
WHERE SDO_WITHIN_DISTANCE(t6.LOCATION,
t4.LOCATION,
'DISTANCE=1000 UNIT=M') = 'TRUE'
and t4.WBBH IN (-123)
and t6.fasj BETWEEN
TO_DATE('20000101000000', 'YYYYMMDD HH24MISS') AND
TO_DATE('20130809000000', 'YYYYMMDD HH24MISS')
118
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
