oracle常用语句

分组去重取第一条数据

SELECT *       
    FROM (SELECT ROW_NUMBER() OVER(PARTITION BY x ORDER BY y DESC) rn,       
          t.*       
          FROM test1 t)       
   WHERE rn = 1  ;   

---

存在更新，不存在插入

MERGE INTO atable a  
USING (  
  SELECT  *
  FROM btable 
  ) b  
ON (a.id = b.id)  
WHEN MATCHED THEN  
  UPDATE SET a.name=b.name,a.f_account=b.f_account
WHEN NOT MATCHED THEN  
  INSERT (a.id,a.name, a.f_account) 
  VALUES (b.id,b.name,b.f_account); 
 

---

查找重复数据

select t.* from table t
where t.name in (select   t.name from table t group by t.name having count(t.name) > 1)
order by t.name desc;
 

---

取改时间字段数据为今天的数

SELECT * FROM lr_base_log a 
WHERE 
TO_CHAR(a.f_operatetime,'yyyy-mm-dd')=TO_CHAR(SYSDATE,'yyyy-mm-dd');

---

日期比较

第一种：<,>, <= , >=
  select * from 表名 where 日期列 >= to_date('2015-10-20 00:00:00','yyyy-mm-dd hh24:mi:ss') and t.日期列 <= to_date('2015-10-20 23:59:59','yyyy-mm-dd hh24:mi:ss')
•	1
第二种 between and
select * from 表名 where 日期列 between to_date('2015-10-20 00:00:00','yyyy-mm-dd hh24:mi:ss')and to_date('2015-10-20 23:59:59','yyyy-mm-dd hh24:mi:ss')