(1)查找这个XXXX表,以to_timestamp字段来查,从2018-09-05 17:25:00查到现在。
select * from XXXX
where statime = to_timestamp('2018-09-05 17:25:00','yyyy-mm-dd hh24:mi:ss')
(2)查看这个XXXXr表 两个时间段之间的statime,statcount两个字段的数据
select statime,statcount from XXXX
where statime >= to_timestamp('2018-09-07 09:00:00','yyyy-mm-dd hh24:mi:ss')
and statime <= to_timestamp('2018-09-07 09:30:00','yyyy-mm-dd hh24:mi:ss')
and region = 0
GROUP BY statime,statcount
(3)先查 select from XXXX where jgsj>sysdate-5/(2460)
(4)我想查都是早上6:10的数据,和夜晚12:05时间段的数据 (限定下时间)
Select from XXXX where jgsj>to_date('2018/08/13 06:00:00','yyyy/MM/dd hh24:mi:ss') and jgsj
(5)查看这个时间段的字段 ,在这个时间段有多少数据
Select count(1) from XXXX where jgsj>to_date('2018/08/13 06:00:00','yyyy/MM/dd hh24:mi:ss') and jgsj
(6)下面语句指的是导出这个XXXX表20180813的那天数据
Select XXXX partiton(P20180813)
(7)帅选riqi = '2018-08-04'和 hphm = '80281' 这两个字段值,并以ftime字段倒序排列;
select * from XXXX where riqi = '2018-08-04' and hphm = '80281' order by ftime
(8)查看这个XXXX表该时间段的最大值
(9)select max(statcount) from XXXX where statime >= to_timestamp('2018-09-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and statime <= to_timestamp('2018-09-03 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
(10)查询这个表在那个时间段的statime,statcount,egion,zoneame字段内容
select statime,statcount,egion,zoneame from XXXX
where statime >= to_timestamp('2018-07-15 12:00:00','yyyy-mm-dd hh24:mi:ss')
and statime <= to_timestamp('2018-07-15 15:45:00','yyyy-mm-dd hh24:mi:ss')
ORDER BY statime
(11)查这个表XXXX的某一天数据
select from XXXX partition(p20180822) order by to_time desc, edit_time desc
(12)清除这个XXXX表 2018-08-15 12:00:00 到现在
delete from XXXX
where statime >= to_timestamp('2018-08-15 12:00:00','yyyy-mm-dd hh24:mi:ss')
(13)select from XXXX where create_time >=to_date('2018-09-11 00:00:00','yyyy-mm-dd hh24:mi:ss') and create_time <=to_date('2018-8-16 23:00:00','yyyy-mm-dd hh24:mi:ss')
(14)查看这个表的前一百条XXXX数据。
select * from XXXX order by ftime desc limit 100;
(15)查这个XXXX表的最大时间(如果数据量比较大要一个当天条件partation,意思就是加分区之类)
select max(jgsk) from XXXX
(16)查这个表XXXX小于系统时间的最大时间。
select max(jgsj) from XXXX where jgsj < sysdate
(17)对XXXX表的分区时间201901月时间内,统计2019-01-17 到 2019-01-18这个时间范围之间的数据。
select count(1) from XXXX partition(P201901) where PASS_TIME>to_timestamp('2019-01-17 00:00:00' , 'yyyy-mm-dd hh24:mi:ss') and PASS_TIME
(18) 筛选XXXX出日期:20190327所有“粤A12216” 车牌
select * from XXXX where t.hphm='粤A12216' and t.jgsj>=to_date('20190327','yyyymmdd') and t.jgsj
(19)查询XXXX表,日期:20190327 ,所有数据,并以 jgsj倒序排列。
select * from XXXX partition(PART_20190327) order by jgsj desc
(20)查询XXXX表,筛选by1字段出不等于所有横,在原来基础上以最新时间排列edittime这个字段,limit代表只显示前面十横。
select * from XXXX where by1 != '1' order by edittime desc limit 10
(21)查询XXXX这张表“PASS_ID”最大值
select max(PASS_ID) from XXXX
(22)筛选XXXX出日期:20191007日筛选出ssid三个编号“'H-92','H-93','H-107'”
select * from XXXX t where t.ssid in ('H-92','H-93','H-107') and t.jgsj>=to_date('20191007','yyyymmdd') and t.jgsj
(23)筛选XXXX出日期:20191008在这天当中的15、16、17、18四个小时中的数据;帅选出ssid三个编号“'H-92','H-93','H-107'”
SELECT * FROM XXXX WHERE SSID IN ('H-92','H-93','H-107')
AND JGSJ>=TO_DATE('2019-10-08','YYYY-MM-DD')
AND TO_CHAR(JGSJ,'HH24') IN ('15','16','17','18')
(24)查询XXXX表,筛选出2019-09-01 00:00:00大于这个时间范围的时间,再筛选TYPE、reason字段分别为2、9基础上再查看第一条数据。
select * from XXXX where TYPE = '2' and reason = '9' and pass_time > '2019-09-01 00:00:00' order by pass_time LIMIT 1
(25)在XXXX表添加一条数据
INSERT INTO "public"."XXXX"("id", "table_name", "ctrl_column", "ctrl_value", "column_type", "column_list", "sync_flag") VALUES (14,
'bas_key_vehicle', 'id', '0', 'Integer', '"id", "hphm", "hpzl", "type", "cpdjr", "lxfs", "cllx"', 1);
(26) 把XXXX表lalarm_pic字段221.1.62.221 替换成192.168.1.173
UPDATE XXXX set lalarm_pic=replace(lalarm_pic,'221.1.62.221','34.120.62.173');
(27) 筛选出车牌 '粤PA0325' 在to_date时间范围内的,只查看ssid,hphm字段。
select ssid,hpbhm from XXXX where Hpbhm in '粤PA0325' and jgsj >=to_date('2019-12-13 10:10:01','YYYY-MM-DD HH24:MI:SS') and jgsj <= to_date('2019-12-14 10:10:01','YYYY-MM-DD HH24:MI:SS')
group by ssid,hpbhm
(28) 筛选出XXXX表的crossing_id='803'这一横。
select * from XXXX t where t.crossing_id='803'
(29) 筛选出XXXX表crossing_id='803'这一横,并对这个表tnode的值更新为919。
update XXXX t set tnode ='919' where t.crossing_id='803'
(30)用AAAA的表的以t5(t5.hpbhm,t5.hpzl)字段去匹配XXXX表(t2.syr,t2.sjhm,t2.hpbhm,t2.hpzl)字段,t5.hpbhm=t.hphm and t5.hpzl=t.hpzl代表他们的值最终等于t5。
SELECT t5.hpbhm,t5.hpzl,t.lsyr,t.lsjhm from AAAA t5 left join (
SELECT t2.lsyr,t2.lsjhm,t2.hpbhm,t2.hpzl from XXXX t2)t on t5.hphm=t.hpbhm and t5.hpzl=t.hpzl
觉得不错欢迎点赞,谢谢