oracle常用语法
时间:2022-03-15 11:42
1,case用法
SELECT CASE WHEN T.FLAG=‘0‘ THEN T.USERID WHEN T.FLAG=‘1‘ THEN T.ORGID ELSE NULL END AS ‘标识‘ FROM XTXMXX T
2,decode用法
--如果FLAG等于1,则转为USERID,如果为0,则转为ORGID,其他的为2 SELECT DEcode(T.FLAG,1,T.USERID,0,T.ORGID,2) FROM XTXMXX T ;
3,创建sequence
-- Create sequence 创建sequence create sequence T41_R_ITEM_FIELDKEY minvalue 1 maxvalue 99999999999999 start with 10220 increment by 1 cache 20;
4,三种连接
--右连接 反方向连接 SELECT * FROM SX02_PROGRAM T,SX03_GL_ACCASS L WHERE T.PROGRAMNO(+)=L.CITEM_ID; --左链接 SELECT * FROM SX02_PROGRAM T,SX03_GL_ACCASS L WHERE T.PROGRAMNO=L.CITEM_ID(+); --特殊连接 SELECT T.PROGRAMNO, (SELECT P.BONDNO FROM SX05_CONTRACT P WHERE P.PROGRAMNO=T.PROGRAMNO) FROM SX02_PROGRAM T,SX03_GL_ACCASS L WHERE T.PROGRAMNO=L.CITEM_ID;
5,更新多列
--多列更新 UPDATE QJGL T SET (T.ZJQHZJRQ,T.ZJQHZJJE,T.LJQHZJJE)= ( --此处只能查询单一值,故要关联 SELECT TT.ZJZJZJRQ,TT.ZJZJZJJE,TT.LJZJZJJE FROM QJGL TT WHERE T.XTXMBH=TT.XTXMBH );
6,快速创建表
--快速创建表语句 CREATE TABLE QJGL AS SELECT * FROM QJGLXX_ZQL T WHERE 1=0;
7,删除表
DROP TABLE TABLE_NAME;
8,删除用户
drop user istaudit cascade;
9.ROW_NUMBER分组排序
select cc.serialno, cc.objectno, cc.corpuspaysum, --PARTITION BY:分组 ORDER BY:排序 可正序倒序 ROW_NUMBER() OVER(PARTITION BY cc.objectno ORDER BY cc.corpuspaysum desc) as rn from sx01_contract_paysource cc;
10.把数字转换为字符串仍保留0.格式
--取两位小数,且把数字转换为字符串仍保留0.格式 select to_char(round(0.326,2),90.99) || ‘%‘ as result from dual;
--不保留0.格式 select to_char(round(0.326,2)) || ‘%‘ as result from dual;
Oracle常用语法,布布扣,bubuko.com