SQL语句

1、去除重复语句

delete from ZS_PC_DJWD a 
where (a.ST_DATE,a.MAX_VALUE,a.DEVICE_CODE) in (select ST_DATE,MAX_VALUE,DEVICE_CODE from ZS_PC_DJWD group by ST_DATE,MAX_VALUE,DEVICE_CODE having count(*) > 1) 
and rowid not in (select min(rowid) from ZS_PC_DJWD group by ST_DATE,MAX_VALUE,DEVICE_CODE having count(*)>1)

2、更新时间

 merge into AIRPRESSURE_PRE r using (select a.order_id,start_time+(b.id-1)*1/24/60 process_time,time from 
(select order_id,start_time from order_info where order_id = '%s') a, 
(select order_id,time,ROW_NUMBER()  OVER (PARTITION BY order_id ORDER BY time) id from AIRPRESSURE_PRE where order_id = '%s' ) b
 where a.order_id = b.order_id) t 
on (r.order_id = t.order_id and r.time = t.time) 
when matched then 
update set r.time_new = t.process_time"%(order_id,order_id) 

3、将毫秒格式化

UPDATE AIRPRESSURE_PRE  
SET TIME_CALC =  to_date(SUBSTR(to_char(nvl(time_new,time),'yyyy-mm-dd hh24:mi:ss'), 0,17)||'00','yyyy-mm-dd hh24:mi:ss') 
WHERE TIME_CALC is NULL 

4、生成id

select order_id,time,ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY time) from AIRPRESSURE_PRE1 where order_id = 'NJB1912231011ZBE01' 

5、根据时间更新真实值

update (
select * from AIRPRESSURE_PRE a,
(select TIME_CALC as TIME_CALC2,sum(real_value) real_value2 from AIRPRESSURE_PRE
where tag_key in ('DL_GLKY_AI_JBYKZG_JL','DL_GLKY_AI_ZSYKZG_JL')
GROUP BY TIME_CALC) t
where a. TIME_CALC = t.TIME_CALC2 and tag_key like '%total%'
)
set time_calc = TIME_CALC2

6、有记录则更新没有则插入

merge into AIRPRESSURE_PRE a using
( SELECT SUM(PRE_VALUE)  PRE_VALUE,TIME_CALC  
FROM AIRPRESSURE_PRE N,(SELECT INSTANT_TAG FROM ENERGY_COMPRESSEDAIR_TAG WHERE PID = '6' ) V 
WHERE N.TAG_KEY = V.INSTANT_TAG AND  to_char(TIME_CALC,'yyyy-mm-dd') = to_char((sysdate),'yyyy-mm-dd') 
group by N.TIME_CALC ) b 
ON(a.TIME_CALC = b.TIME_CALC and a.TAG_KEY='%s') 
WHEN matched 
THEN UPDATE SET a.PRE_VALUE = b.PRE_VALUE 
WHEN NOT matched 
THEN INSERT (a.TAG_KEY,a.PRE_VALUE,a.TIME_CALC)values('%s',b.PRE_VALUE,b.TIME_CALC)"%(instant_tags_ft2206,instant_tags_ft2206)

7、不要一些tag点 not in

SELECT DISTINCT TAG_KEY,TO_CHAR(TIME_CALC,'yyyy-mm-dd') TIME_date 
FROM AIRPRESSURE_PRE 
WHERE TIME_CALC IS not NULL 
and tag_key not in ('hz_total1','hz_total2','hz_total3')