Oracle SQL 實用技巧 12 選：條件邏輯、隨機抽樣、字串處理、合併語法等

1. SELECT 中使用 CASE 條件邏輯

SELECT ename, sal,
       CASE WHEN sal <= 2000 THEN 'UNDERPAID'
            WHEN sal >= 4000 THEN 'OVERPAID'
            ELSE 'OK' END AS status
FROM emp;

2. 隨機抽取 N 筆資料

SELECT * FROM (
    SELECT ename, job FROM emp ORDER BY DBMS_RANDOM.VALUE()
) WHERE ROWNUM <= 5;

3. 依子字串排序（如職稱後 2 碼）

SELECT ename, job FROM emp
ORDER BY SUBSTR(job, LENGTH(job)-1);

4. NULL 排序控制

SELECT ename, sal, comm FROM emp
ORDER BY comm NULLS LAST;

5. 動態排序鍵值（依職位決定排序欄位）

SELECT ename, sal, job, comm FROM emp
ORDER BY CASE WHEN job = 'SALESMAN' THEN comm ELSE sal END;

6. 找出另一張表不存在的值（MINUS 集合運算）

SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp;

7. 運算與比較中處理 NULL（COALESCE）

SELECT ename, comm, COALESCE(comm,0)
FROM emp
WHERE COALESCE(comm,0) < (SELECT comm FROM emp WHERE ename = 'WARD');

8. 刪除重複資料（保留 MIN ID）

DELETE FROM dupes
WHERE id NOT IN (SELECT MIN(id) FROM dupes GROUP BY name);

9. MERGE 合併語法（UPSERT + 刪除）

MERGE INTO emp_commission ec
USING (SELECT * FROM emp) e
ON (ec.empno = e.empno)
WHEN MATCHED THEN
    UPDATE SET ec.comm = 1000
    DELETE WHERE (sal < 2000)
WHEN NOT MATCHED THEN
    INSERT (ec.empno, ec.ename, ec.deptno, ec.comm)
    VALUES (e.empno, e.ename, e.deptno, e.comm);

10. 用 SQL 產生 SQL（動態建立統計指令）

SELECT 'SELECT COUNT(*) FROM ' || table_name || ';' FROM user_tables;

11. 計算字元在字串中出現次數

SELECT (LENGTH('10,CLARK,MANAGER') - LENGTH(REPLACE('10,CLARK,MANAGER',',',''))) / LENGTH(',') AS cnt FROM dual;

12. 字母數字分離（單一欄位存 ENAME+SAL）

SELECT REPLACE(TRANSLATE(data,'0123456789','0000000000'),'0') AS ename,
       TO_NUMBER(REPLACE(TRANSLATE(LOWER(data),'abcdefghijklmnopqrstuvwxyz',RPAD('z',26,'z')),'z')) AS sal
FROM (SELECT ename||sal AS data FROM emp);

結果：SMITH 800、ALLEN 1600 ...