Oracle SQL常用语句

1.多表连接创建视图

create or replace view V_SI_COR_CORTYPE as
select M.AAA103 as Name,'单位类型分析' as Category,M.value as value from
(
select * from
(select t.AAB019, count(t.AAB019) as value from AB01 t group by t.AAB019) A
left join
(select distinct AAA102,AAA103 from aa10 where AAA100='AAB019') S
on A.AAB019=S.AAA102
)M
where M.aaa103 is not null order by M.value desc

2.查询匹配字段

select t.AAA100,t.AAA103 from AA10 t where t.AAA103 like '%派遣%'

3.求平均值

select t.aab001,sum(t.AAE180)/count(t.aab001) as Avg_money from ac04 t group by t.aab001

4.插入更新表数据

Insert into corporation(CORID,name)
select AAB001,AAB004 FROM socialsecurity.AB01

5.通过一张表去更新另一张表（多字段）

在遇到“单行子查询返回多个行”错误时，需加上ROWNUM = 1

UPDATE ZDB_REGION_GEOCOOD T
   SET (T.CODE) =
       (SELECT S.PROVINCECODE
          FROM ZDB_REGION S
         WHERE T.NAME IN S.PROVINCENAME
           AND ROWNUM = 1)
WHERE EXISTS (SELECT 1
          FROM ZDB_REGION M
         WHERE T.NAME = M.PROVINCENAME
           AND ROWNUM = 1)

6.Oracle 选择集自定义排序

（1）Case when

oder by case when t.xb='男' then 0 when t.xb='女' then 1 end asc

（2）Decode

ORDER BY DECODE(T.NAME,'A','1','B','2','C','3',T.NAME)

7、Case When用法

（1）自定义排序

select * from SAMPLE t oder by case when t.xb='男' then 0 when t.xb='女' then 1 end asc

（2）汉化编码

select case t.code when 1 then '男' when 2 then '女' end,t.name from SAMPLE t

（3）条件判断

select case t.xb when '1' then count(*) when '2' then -count(*) end as value from SAMPLE t

（4）空值用0填充

CASE WHEN T.NAME is null THEN 0 ELSE T.NAME END

8.选取前几条数据

select * from table where rownum <=100

9.截取字符串

substr(A.AAC004,1,1)--从第一位开始，截取长度1

10.根据出生日期计算年龄

round(months_between(SYSDATE,to_date(出生日期,'yyyymmdd'))/12)

11.小数处理函数

① FLOOR——对给定的数字取整数位

FLOOR(2345.67)---------------2345

② CEIL-- 返回大于或等于给出数字的最小整数

CEIL(3.1415927)---------------4

③ ROUND——按照指定的精度进行四舍五入

ROUND(3.1415926,4)---------3.1416

④ TRUNC——按照指定的精度进行截取一个数

TRUNC(3.1415926,4)----------3.1415

12.分组求和

（1）group by

select  t.type, sum(t.value) as sum from  A t group by t.type

（2）partition by

select  t.type,  sum(t.value) over(partition by t.type) as sum from A  t

13.计算百分比

SELECT S.INDUSTRY,COUNT(S.INDUSTRY),
round((COUNT(S.INDUSTRY)-----分子
/
(select count(*) from COR_TYPE S where S.INDUSTRY is not null)-----分母
),2)*100||'%' 
FROM COR_TYPE S
GROUP BY S.INDUSTRY

14.格式转换

（1）字符串转数值

cast(t.value as number)

（2）数值转字符串

cast(t.value as varchar(2000))

（3）字符串转日期

to_date(t.value, 'yyyyMMdd')

（4）日期转字符串

to_char(sysdate, 'yyyyMMdd'),

15.分组后取第一个

SELECT * FROM (
SELECT T.*, ROW_NUMBER() OVER (PARTITION BY PATIENT_ID ORDER BY DRUGNUM) RN FROM TABLE T)
WHERE RN = 1

取DRUGNUM最小的那一列显示