一行拆成多列/一行转多列/一行转成多行/一行拆成多行：
select regexp_substr(‘1:a,2:b,3:c’, ‘[^,]+’, 1, level) col from dual connect by level <= regexp_count(‘1:a,2:b,3:c’, ‘\,’) + 1
按逗号拆分字串/一行转成多行：WITH TMP_TAB as (SELECT ‘15,13,12,15,16,41,13,16’ as id,ROWNUM FROM dual) SELECT ROWNUM,max(id),COUNT(id) FROM (SELECT REGEXP_SUBSTR(id, ‘[^,]+’, 1, ROWNUM) as id FROM TMP_TAB CONNECT BY ROWNUM <= length(id) – length(replace(id, ‘,’, ”)) + 1) GROUP BY ROWNUM ORDER BY ROWNUM
把数据库里面的某个字段进行多行并合： SELECT to_char(WM_CONCAT(pmd02)) FROM pmd_file WHERE pmd01=’110009′
查暂时表中的表名,需要SYS登陆： select * from dba_objects where object_name like ‘% TEMP_CBMR622622%’
SQL中含有中文：select ima01 from ima_file where asciistr(ima01) like ‘%\%’;
查看当前数据的组成类型：SELECT dump(bmb03) FROM bmb_file WHERE bmb03 LIKE ‘%518408210043’
SQLERRD[3]：处理资料的笔数；SQLERRD[2]：新增时 SERIAL 字段所传回之值；SQLERRD[6]：最后一个 ROWID 值；SQLAWARN[1]：若第２至第８字符中任意一个被设成”W”，则此字符亦为 “W”，否则为空白；SQLAWARN[2]：若资料太长而被截掉时，会被设成 “W”；SQLAWARN[3]：若 aggregate function(如 SUM，AVG，MAX，MIN) 处理时遇到 NULL 值，则会被设成”W”；SQLAWARN[4]：若查询时，若欲查询的字段数目和 INTO 之变量数目不合时，会被设成 “W”；用法：if sqlca.sqlerrd[6] then
取最近采购单价:
SELECT Max(pmn31) Keep (dense_rank First Order By pmm04 ASC) price_lastt,
Max(pmn31t) Keep (dense_rank First Order By pmm04 ASC) price_last
FROM pmn_file,pmm_file WHERE pmm01=pmn01 AND pmm09=? AND pmn04=? GROUP By pmm09,pmn04
SELECT ZGH,MIN(WM) KEEP(DENSE_RANK FIRST ORDER BY WM), MIN(RQ) KEEP(DENSE_RANK FIRST ORDER BY WM)
FROM T_JZG GROUP BY ZGH
如何取数据列表中的最后一条记录(关键点是为rownum取别名)：select * from (select rownum rn,table_name.* from table_name) where rn=(select count(*) from table_name)
赋予公共权限。GRANT select ON films FROM PUBLIC; 取消REVOKE select ON films FROM PUBLIC;
接口问题处理：用tiptop登录，先找出该服务 ps -ef|grep as.xcf_84 ，kill掉，再重启。重启命令：startws（已设置自动）
转载请注明：赫非域 » 常用ORACLE SQL记录