- 子查詢：
SELECT * FROM a WHERE x IN (SELECT y FROM b)
- 最新回覆時間：
SELECT a.title, a.username, b.adddate FROM a, (SELECT MAX(adddate) adddate FROM a GROUP BY title) b
- 外部聯結：
SELECT * FROM a LEFT OUTER JOIN b ON a.id = b.id
- 內嵌檢視：
SELECT * FROM (SELECT a,b,c FROM t) T WHERE T.a > 1
- BETWEEN：含邊界
WHERE dt BETWEEN '2023-01-01' AND '2023-12-31'
- IN / NOT IN：
WHERE status IN ('A','B','C')
- 刪除主表無對應從表資料：
DELETE FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE a.id = b.a_id)
- 多表聯結：
SELECT * FROM a JOIN b ON ... JOIN c ON ... JOIN d ON ...
- 排程提醒：
SELECT * FROM schedule WHERE DATEDIFF(minute, start_time, GETDATE()) > 5
- 分頁 (SQL Server)：
SELECT TOP 10 b.* FROM (
SELECT TOP 20 pk, sort_col FROM tbl ORDER BY sort_col DESC
) a JOIN tbl b ON b.pk = a.pk ORDER BY a.sort_col
- 前 N 筆：
SELECT TOP 10 * FROM tbl WHERE ...
- 分組取最大值完整列：
SELECT a,b,c FROM t ta
WHERE a = (SELECT MAX(a) FROM t tb WHERE tb.b = ta.b)
- 集合差集：
(SELECT x FROM A) EXCEPT (SELECT x FROM B) EXCEPT (SELECT x FROM C)
- 隨機 10 筆：
SELECT TOP 10 * FROM tbl ORDER BY NEWID()
- 刪除重複列 (保留最大 ID)：
DELETE FROM t WHERE id NOT IN (
SELECT MAX(id) FROM t GROUP BY col1, col2, ...
)
- 列出所有使用者資料表：
SELECT name FROM sysobjects WHERE xtype = 'U'
- 列出資料表所有欄位：
SELECT name FROM syscolumns WHERE id = OBJECT_ID('TableName')
- 交叉表/樞紐邏輯 (CASE)：
SELECT type,
SUM(CASE vender WHEN 'A' THEN pcs ELSE 0 END) AS A,
SUM(CASE vender WHEN 'B' THEN pcs ELSE 0 END) AS B,
SUM(CASE vender WHEN 'C' THEN pcs ELSE 0 END) AS C
FROM tbl GROUP BY type
- 清空資料表 (保留結構)：
TRUNCATE TABLE table1
- 取第 10-15 筆：
SELECT TOP 5 * FROM (
SELECT TOP 15 * FROM tbl ORDER BY id ASC
) t ORDER BY id DESC
- 將 EXEC 結果存入變數：
DECLARE @num INT, @sql NVARCHAR(4000)
SET @sql = 'SELECT @a = COUNT(*) FROM TableName'
EXEC sp_executesql @sql, N'@a INT OUTPUT', @num OUTPUT
SELECT @num
