1. 刪除重復記錄（臨時表法）
SELECT DISTINCT * INTO #tmp FROM TableName;
DELETE FROM TableName;
SELECT * INTO TableName FROM #tmp;
DROP TABLE #tmp;
-- 前提：開啟 'select into' 選項
EXEC sp_dboption 'DBName', 'select into', 'true';
2. 縮減事務日志（多次執行見效）
BACKUP LOG DBName WITH NO_LOG;
BACKUP LOG DBName WITH TRUNCATE_ONLY;
DBCC SHRINKDATABASE(DBName);
-- 現代版本（2008+）建議：切 SIMPLE → SHRINKFILE → 切 FULL → 完整備份
3. 日期時間格式化速查
| 需求 | 代碼 | 輸出示例 |
|---|
| 時分秒 | CONVERT(VARCHAR(8), GETDATE(), 108) | 14:30:05 |
| 年月日 | CONVERT(VARCHAR(10), GETDATE(), 120) | 2024-01-15 |
| 年月 | CONVERT(VARCHAR(7), GETDATE(), 120) | 2024-01 |
| 年月 (拼接) | CAST(YEAR(GETDATE()) AS VARCHAR) + '-' + RIGHT('0'+CAST(MONTH(GETDATE()) AS VARCHAR),2) | 2024-01 |
4. 條件表達式 (CASE)
-- 簡單 CASE
SELECT a, CASE b WHEN '11' THEN '細細' WHEN '22' THEN '呵呵' ELSE '哈哈' END AS 轉換 FROM Table1;
-- 搜索 CASE
SELECT a, b, CASE WHEN c = '111' THEN '細細' WHEN c = '222' THEN '呵呵' ELSE '哈哈' END AS 轉換1 FROM Table1;
5. 查詢結果導出文本文件 (BCP)
EXEC master..xp_cmdshell 'bcp "SELECT * FROM DB.dbo.Table" queryout "D:\Export.txt" -c -t, -T -S localhost -C 65001';
-- -c 字符模式 -t 分隔符 -T 信任連接 -C 代碼頁(65001=UTF-8)
6. ROUND 四捨五入
DECLARE @v FLOAT = 0.1566134;
PRINT ROUND(@v, 3); -- 0.157
7. 自動收縮數據庫（慎用）
EXEC sp_dboption 'DBName', 'autoshrink', 'TRUE';
-- 建議：定時任務 SHRINKFILE 替代，避免反復收縮/增長碎片
8. 去除首尾指定字符
DECLARE @s VARCHAR(20) = ',,,1->1,';
WHILE LEFT(@s,1) = ',' SET @s = STUFF(@s,1,1,'');
WHILE RIGHT(@s,1) = ',' SET @s = STUFF(REVERSE(@s),1,1,'');
SELECT @s; -- 1->1
9. 刪除重復僅保留一行（三種寫法）
-- 法一：NOT IN 保留最小 ID
DELETE FROM A WHERE UserID NOT IN (SELECT MIN(UserID) FROM A GROUP BY UserName, UserPwd);
-- 法二：EXISTS 保留最小 ID
DELETE FROM A WHERE EXISTS (SELECT 1 FROM A b WHERE A.UserName=b.UserName AND A.UserPwd=b.UserPwd AND A.UserID < b.UserID);
-- 法三：ROW_NUMBER (2005+)
WITH cte AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY UserName,UserPwd ORDER BY UserID) rn FROM A)
DELETE cte WHERE rn > 1;
10. 遞歸查詢路徑（表值函數）
CREATE FUNCTION f_Path(@Start VARCHAR(10))
RETURNS @t TABLE (Path VARCHAR(1000), St VARCHAR(20), Ed VARCHAR(20), Km INT, Lvl INT)
AS BEGIN
DECLARE @i INT = 1;
INSERT @t SELECT St+'-'+Ed, St, Ed, Km, @i FROM Routes WHERE St = @Start;
WHILE EXISTS (SELECT 1 FROM Routes r JOIN @t t ON t.Ed = r.St AND t.Lvl = @i AND t.Ed <> @Start)
BEGIN
SET @i += 1;
INSERT @t SELECT t.Path+'-'+r.Ed, r.St, r.Ed, t.Km+r.Km, @i
FROM Routes r JOIN @t t ON t.Ed = r.St AND t.Lvl = @i-1 AND t.Ed <> @Start;
END
RETURN;
END;
-- 調用
SELECT Path, Km FROM dbo.f_Path('A');
11. 分組取最新 N 條 & 統計
-- 按類別取最新前 N 條
SELECT * FROM T t1 WHERE NOT EXISTS (
SELECT 1 FROM T t2 WHERE t2.ClassCode = t1.ClassCode AND t2.ClassID > t1.ClassID
); -- 相關子查詢法
-- 分組統計個數
SELECT ClassCode, COUNT(*) FROM T GROUP BY ClassCode;
12. 分組字符串聚合（標量函數）
CREATE FUNCTION fc_Concat(@PID VARCHAR(10)) RETURNS VARCHAR(100) AS BEGIN
DECLARE @s VARCHAR(1000) = '';
SELECT @s += ',' + PositionID FROM TB WHERE ProductID = @PID;
RETURN STUFF(@s,1,1,'');
END;
SELECT ProductID, dbo.fc_Concat(ProductID) AS Positions FROM TB GROUP BY ProductID;
13. 每組 Top N（三種經典寫法）
-- 法一：IN + TOP
SELECT * FROM Test A WHERE Id IN (SELECT TOP 3 Id FROM Test WHERE h_id = A.h_id);
-- 法二：NOT EXISTS
SELECT * FROM Test A WHERE NOT EXISTS (SELECT 1 FROM Test WHERE h_id = A.h_id AND Id > A.Id); -- 保留每組最小 3 個需調整
-- 法三：COUNT 計數
SELECT * FROM Test A WHERE (SELECT COUNT(*) FROM Test WHERE h_id = A.h_id AND Id <= A.Id) <= 3;
14. 區間分組統計
DECLARE @p INT = 10;
SELECT CAST(p*@p AS VARCHAR) + '-' + CAST((p+1)*@p AS VARCHAR) AS Range, Num
FROM (
SELECT (Weight/@p) AS p, COUNT(*) AS Num
FROM @t WHERE Weight BETWEEN 10 AND 100
GROUP BY (Weight/@p)
) a;
15. IN 子句自定義順序
SELECT * FROM @t WHERE Id IN (2,4,3)
ORDER BY CHARINDEX(','+CAST(Id AS VARCHAR)+',', ',2,4,3,');
-- 強制按 2,4,3 順序輸出
