掌握這些經典 SQL 模式,能顯著提升開發效率與查詢效能。以下按功能分類整理,附帶說明與適用場景。
SELECT * INTO new_table FROM source_table WHERE 1 <> 1;
原理: WHERE 1 <> 1 條件永不成立,僅建立空表結構。
INSERT INTO target_table (col_a, col_b, col_c) SELECT col_d, col_e, col_f FROM source_table;
注意: 目標表需預先存在,欄位順序與資料型別需對應。
SELECT a.title, a.username, b.latest_reply
FROM articles a
JOIN (
SELECT title, MAX(adddate) AS latest_reply
FROM articles
GROUP BY title
) b ON a.title = b.title;
SELECT a.col1, a.col2, a.col3, b.col3, b.col4, b.col6 FROM table_a a LEFT OUTER JOIN table_b b ON a.col1 = b.col3;
SELECT * FROM schedule WHERE DATEDIFF(minute, start_time, GETDATE()) > 5;
DELETE FROM main_table
WHERE NOT EXISTS (
SELECT 1 FROM sub_table
WHERE main_table.id = sub_table.id
);
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 TO_CHAR(UPD_DATE, 'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')
) X
JOIN (
SELECT NUM, UPD_DATE, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE, 'YYYY/MM') =
TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') || '/01', 'YYYY/MM/DD') - 1, 'YYYY/MM')
) Y ON X.NUM = Y.NUM
WHERE X.INBOUND_QTY + NVL(Y.STOCK_ONHAND, 0) <> X.STOCK_ONHAND
) B ON A.NUM = B.NUM;
SELECT * FROM student_info si
WHERE NOT EXISTS (
SELECT 1 FROM student s WHERE si.id = s.id
)
AND si.department = :dept_name
AND si.major = :major_name
ORDER BY si.gender, si.origin, si.exam_score DESC;