Oracle SQL常用语句

1，查询插入

insert into  user_role(account_id, role_id, create_user)
select t.employee_id, 'BC8FBF8B1D9843A2AE83B2310AC57C28', 'ADMIN' from ACCOUNT t where employee_id not in (select account_id from user_role)

2, 按年查询

where o.YEAR=EXTRACT(YEAR FROM SYSDATE)

3, 分组去重

select * from (
    SELECT ROW_NUMBER() OVER(PARTITION BY F1 ORDER BY F2 DESC) RN
      FROM XX AP
)
T WHERE T.RN = 1

4，查询当天记录

select * from table where trunc(create_date)=trunc(sysdate)

 或者

create_date between to_date('2017/12/21', 'yyyy/mm/dd') and  to_date('2017/12/22', 'yyyy/mm/dd')

5, md5密码加密

select Utl_Raw.Cast_To_Raw(sys.dbms_obfuscation_toolkit.md5(INPUT_STRING =>'123456')) from dual;

6, 计算相似度

SELECT SYS.UTL_MATCH.edit_distance_similarity(title, 'xxxx') as 相似度

 7, 快速导数据

create table aa as select * from table

8, 根据状态值建立索引

需求：某表中数据status = 1的字段不能重复

CREATE UNIQUE INDEX UK_STUDIO_ASSISTANT ON STUDIO_ASSISTANT ( 
       DECODE (status, 1, employee_no, id )
 );

id为guid列，状态为1的员工编号记录只允许有一条

 9, Update From

Oracle没有Update from，但通过下面方式可以实现。

UPDATE A SET A.NAME=(SELECT B.NAME FROM B WHERE B.ID=A.ID)

UPDATE DOCTOR d
SET d.IS_STRDIO='是'
WHERE EXISTS (SELECT 1 FROM register r 
              LEFT JOIN team_member m  ON m.open_id = r.open_id
              WHERE m.status = 1
              AND r.name=d.DOCTOR_NAME
              AND r.tel=d.DOC_TELEPHONE)

根据两个字段匹配更新

10, Oracle中between是前后都包含关系

11, 更改当前查询用户

alter session set current_schema=UCenter

 

         SELECT d."DISTRICT_CODE",                d."REGION_CODE",                d."SUB_AREA_CODE",                COUNT(1) being_num,                SUM(case when d."JOIN_DATE" >= to_date('201904', 'yyyy-MM') then 1 else 0 end) coming_num           FROM EIS_USER.VIEW_USER_DETAIL d          WHERE d."STATUS" = 1            AND d."DISTRICT_CODE" = 14            AND d."JOB_SORT" = 100            AND d."YEAR" = 2019            AND d."MONTH" = 4       GROUP BY d."DISTRICT_CODE",                d."REGION_CODE",                d."SUB_AREA_CODE"       ORDER BY d."REGION_CODE"