[SQL] 筆記

整理實務上常遇到的。

查詢資料

取前幾筆

DB2

SELECT * FROM zoo FETCH FIRST 1 ROWS ONLY

取後幾筆

DB2

SELECT * FROM zoo ORDER BY critter DESC FETCH FIRST 1 ROWS ONLY

分頁查詢

DB2

SELECT * FROM (SELECT rownumber() OVER (ORDER BY critter) as ROWID, a.* FROM zoo a) WHERE ROWID BETWEEN 11 AND 20

串接 Group 後的欄位

DB2

SELECT category, LISTAGG(id, ', ') as ids FROM myTable GROUP BY category;

取得當前的日期

DB2

SELECT VARCHAR_FORMAT(CURRENT_DATE, 'YYYYMMDD') FROM SYSIBM.SYSDUMMY1;
SELECT VARCHAR_FORMAT(CURRENT_DATE, 'YYYYMMDD') FROM mytable;
--或是
SELECT date(current timestamp) FROM SYSIBM.SYSDUMMY1;
SELECT date(current timestamp) FROM mytable;

取得當前的日期和時間

DB2

SELECT VARCHAR_FORMAT(CURRENT TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM SYSIBM.SYSDUMMY1;

取得去年的日期

DB2

SELECT VARCHAR_FORMAT(CURRENT_DATE - 1 YEAR, 'YYYYMMDD') FROM SYSIBM.SYSDUMMY1;

查詢欄位值為純數字

DB2

SELECT * FROM mytable WHERE REGEXP_LIKE(mycolumn, '^[0-9]+$')

找出在 table1 中但不在 table2 中的記錄

使用 EXCEPT 運算子

SELECT * FROM table1
EXCEPT
SELECT * FROM table2;

使用 NOT EXISTS

SELECT *
FROM table1 t1
WHERE NOT EXISTS (
    SELECT 1 
    FROM table2 t2 
    WHERE t1.key_column = t2.key_column
);

找出重複的截取值

例如找出前8碼重複資料

SELECT *
FROM users
WHERE substr(idn, 1, 8) IN (
    SELECT substr(idn, 1, 8)
    FROM users
    GROUP BY substr(idn, 1, 8)
    HAVING COUNT(*) > 1
)
ORDER BY idn;

如果該欄位有index，可改用 EXISTS 和 LIKE 找出重複的記錄

SELECT *
FROM users u1
WHERE EXISTS (
    SELECT 1
    FROM users u2
    WHERE u2.idn LIKE CONCAT(substr(u1.idn, 1, 8), '%')
      AND u2.idn != u1.idn  -- 排除自己
)
ORDER BY idn

如果該欄位有index，且長度固定，為純數字

SELECT *
FROM users u1
WHERE EXISTS (
  SELECT 1
  FROM users u2
  WHERE u2.idn <> u1.idn  -- 排除自己
    AND u2.idn >= CONCAT(LEFT(u1.idn, 8), '00000000')
    AND u2.idn <= CONCAT(LEFT(u1.idn, 8), '99999999')
)
ORDER BY idn

修改資料

插入不存在的記錄

INSERT INTO target_table (id, name, status, created_date)
SELECT ?, ?, ?, CURRENT_TIMESTAMP
FROM SYSIBM.SYSDUMMY1
WHERE NOT EXISTS (
    SELECT 1 FROM target_table WHERE id = ?
);

使用暫存表處理大量筆數更新

-- 1. 建立暫存表存放 id 清單
CREATE TABLE temp_id_list (id VARCHAR(50));

-- 2. 插入多筆 id 資料
INSERT INTO temp_id_list VALUES ('id1'), ('id2'), ('id3')...;

-- 3. 使用 UPDATE 搭配 EXISTS 更新
UPDATE table1 t1
SET flag = 'Y'
WHERE EXISTS (SELECT 1 FROM temp_id_list t WHERE t.id = t1.id);

-- 4. 刪除暫存表
DROP TABLE temp_id_list;

修改結構

新增欄位

DB2

ALTER TABLE mytable
  ADD COLUMN mycolumn1 VARCHAR (45) DEFAULT
  ADD COLUMN mycolumn2 VARCHAR (20) DEFAULT;

調整欄位大小

DB2

ALTER TABLE mytable 
  ALTER COLUMN mycolumn1 SET DATA TYPE VARCHAR (200)
  ALTER COLUMN mycolumn2 SET DATA TYPE VARCHAR (200);

重新命名欄位

DB2

ALTER TABLE mytable
  RENAME COLUMN mycolumn1 TO mycolumn11;

搬移指定資料到新的資料表

MSSQL

-- 1. 建立新 table 並複製資料
SELECT * 
INTO new_table_name
FROM original_table_name
WHERE your_condition;

-- 2. 從原 table 刪除已搬移的資料
DELETE FROM original_table_name 
WHERE your_condition;

預存程序

DB2
傳回單一結果集，參考https://www.ibm.com/docs/zh-tw/db2/11.5?topic=procedures-returning-result-sets-from-sql

CREATE OR REPLACE PROCEDURE sp_read_emp(IN p_job VARCHAR(10))
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
  DECLARE c_emp CURSOR WITH RETURN FOR
    SELECT salary, bonus, comm
    FROM employee
    WHERE job != p_job;

  OPEN c_emp;
END;

CALL sp_read_emp('PRES');