SQL 進階實用技巧集錦：複製表、跨表刪除、分頁提醒、四表聯查、最小未用 ID、年度電話費交叉表

1. 複製表結構 (不含資料)

SELECT * INTO new_table FROM src_table WHERE 1<>1;

2. 複製資料 (結構已存在)

INSERT INTO dest_table (col_a, col_b, col_c)
SELECT src_d, src_e, src_f FROM src_table;

3. 顯示文章、發文者、最新回覆時間

SELECT a.title, a.username, b.adddate
FROM articles a
JOIN (SELECT title, MAX(adddate) AS adddate FROM replies GROUP BY title) b
  ON a.title = b.title;

4. 外部聯結查詢

SELECT a.a, a.b, a.c, b.c, b.d, b.f
FROM table_a a LEFT OUTER JOIN table_b b ON a.a = b.c;

5. 行程提醒：提前 5 分鐘

SELECT * FROM schedule 
WHERE DATEDIFF(minute, start_time, GETDATE()) > 5;

6. 刪除主表中已無對應副表的資料

DELETE FROM info
WHERE NOT EXISTS (SELECT 1 FROM infobz WHERE info.infid = infobz.infid);

7. 複雜四表聯查 (Oracle 語法轉 SQL Server)

SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
FROM TABLE1 A
JOIN (
    SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE AS PREV_UPD_DATE
    FROM (
        SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND
        FROM TABLE2
        WHERE YEAR(UPD_DATE) = YEAR(GETDATE()) AND MONTH(UPD_DATE) = MONTH(GETDATE())
    ) X
    LEFT JOIN (
        SELECT NUM, UPD_DATE, STOCK_ONHAND
        FROM TABLE2
        WHERE UPD_DATE = DATEADD(month, -1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
    ) Y ON X.NUM = Y.NUM
    WHERE X.INBOUND_QTY + ISNULL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND
) B ON A.NUM = B.NUM;

8. 學生資料篩選：排除已存在學生

SELECT * FROM studentinfo si
WHERE NOT EXISTS (SELECT 1 FROM student s WHERE si.id = s.id)
  AND si.department = @dept AND si.major = @major
ORDER BY si.gender, si.origin, si.exam_score DESC;

9. 年度電話費交叉表 (月份橫向展開)

SELECT a.userper, a.tel, a.standfee, YEAR(a.telfeedate) AS telyear,
    SUM(CASE WHEN MONTH(a.telfeedate)=1  THEN a.factration END) AS JAN,
    SUM(CASE WHEN MONTH(a.telfeedate)=2  THEN a.factration END) AS FEB,
    ...
    SUM(CASE WHEN MONTH(a.telfeedate)=12 THEN a.factration END) AS DEC
FROM (
    SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration
    FROM TELFEESTAND a JOIN TELFEE b ON a.tel = b.telfax
) a
GROUP BY a.userper, a.tel, a.standfee, YEAR(a.telfeedate);

10. 取得表中最小未使用 ID (缺口查找)

SELECT CASE 
    WHEN EXISTS (SELECT 1 FROM Handle WHERE HandleID = 1) 
    THEN (SELECT MIN(HandleID) + 1 FROM Handle 
          WHERE HandleID NOT IN (SELECT HandleID - 1 FROM Handle))
    ELSE 1 END AS NextAvailableID;

這些片段涵蓋 DDL/DML 常見模式，實務中請根據具體業務邏輯、索引狀況、資料量調整。現代寫法建議：
• 複製表結構 → CREATE TABLE new LIKE src (MySQL) / SELECT TOP 0 * INTO new FROM src (SQL Server)
• 交叉表 → PIVOT / 動態 SQL
• 缺口 ID → ROW_NUMBER() OVER (ORDER BY id) <> id 窗口函數