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最小的那一列显示

 

 
 

posted @ 2017-12-22 14:37  付刚的博客  阅读(122)  评论(0编辑  收藏