--like语句优化
reverse(line)like reverse('AA')
--sql比较两个字符串是否包含
INSTR(MASTER_WO, A.WORK_ORDER) > 0;
--case when 语句
CASE WHEN INSTR(MASTER_WO, A.WORK_ORDER) > 0 THEN 'OK' ELSE '不一致' END RES;
--根据某栏位排序
SELECT CHECK_IN_TIME,
ROW_NUMBER ()
OVER (PARTITION BY SERIAL_NUMBER ORDER BY CHECK_IN_TIME DESC)
RN
FROM table
WHERE sn ='123';
--将字符串时间转化成时间格式比较
TO_DATE( A.WORK_DATE || LPAD( A.WORK_TIME, 2, '0' ) || '0000', 'YYYYMMDDHH24MISS' ) >= TO_DATE( REPLACE ( '2021-01-01', '-', '' ) || '08' || '0000', 'YYYYMMDDHH24MISS' )
TO_CHAR( B.OUT_PROCESS_TIME, 'yyyyMMddhh24miss' ) BETWEEN '20230715' || '00' || '0000' AND '20210802' || '08' || '0000'
--判断字符串空值就赋值为0
NVL (J.FAIL_QTY, 0);
--判断字符串值
DECODE (H.CURRENT_STATUS,
'0', 'PASS',
'9', 'Retest',
'1', 'FAIL');
--截取字符前或后面的字符串
SELECT SUBSTR('KSLRJ423350007_2', 1, INSTR('KSLRJ423350007_2', '_')-1) AS result FROM dual;
SELECT SUBSTR('KSLRJ423350007_2', INSTR('KSLRJ423350007_2', '_')+1, 1) AS result FROM dual;
--查询正在运行的job
SELECT job,
what,
to_char(last_date, 'yyyy - mm - dd hh24:mi:ss') last_date,
to_char(next_date, 'yyyy - mm - dd hh24:mi:ss') next_date,
interval
FROM dba_jobs;
--查询oracle相同行的数据：
select wo,item_ipn from IMES.p_check_bom a
where rowid< (select max(rowid) from IMES.p_check_bom
where wo=a.wo and item_ipn=a.item_ipn);
--查询数据库表的数据量：
SELECT table_name, num_rows
FROM all_tables
WHERE table_name IN ('P_REWORK_NO');
--分组查询
SELECT
T.PROCESS_ID,
A.PROCESS_NAME,
COUNT( 1 ) RepassQTY
FROM
(
SELECT DISTINCT
A.SERIAL_NUMBER,
A.PROCESS_ID,
A.CURRENT_STATUS,
ROW_NUMBER ( ) OVER ( PARTITION BY A.SERIAL_NUMBER, A.PROCESS_ID ORDER BY A.OUT_PROCESS_TIME DESC ) RN
FROM
table1 A
WHERE
A.OUT_PROCESS_TIME BETWEEN TO_DATE( '2023-09-20 08:00:00', 'YYYY-MM-DD HH24:MI:SS' )
AND TO_DATE( '2023-10-12 20:00:00', 'YYYY-MM-DD HH24:MI:SS' )
AND ROUTE_ID = '110001520'
ORDER BY
SERIAL_NUMBER,
PROCESS_ID
) T,
table2 A
WHERE
1 = 1
AND T.RN > 1 --AND T.CURRENT_STATUS = 0
AND T.PROCESS_ID = A.PROCESS_ID
GROUP BY
T.PROCESS_ID,
A.PROCESS_NAME;
--查询SPE1字符串中第一个逗号和第三个逗号之间的字符串
SELECT SUBSTR(SPEC1, INSTR(SPEC1, ',', 1, 1) + 1, INSTR(SPEC1, ',', 1, 3) - INSTR(SPEC1, ',', 1, 1) - 1) AS result
FROM TABLE_NAME WHERE PART_NO='LA4AU020-CS-H-B';
--查询所有存储当中是否用了tabel_A这个表
SELECT DISTINCT NAME FROM all_source --系统表
WHERE TYPE = 'PROCEDURE' --类型为存储
AND upper(text) LIKE '%tabel_A%';
--查询所有存储中是否使用A字段
select T.OWNER||'.'||t.name,t.line,t.text from all_source t where type = 'PROCEDURE'
and upper(text) like '%A%' order by T.OWNER||'.'||t.name,t.line
