-- 法一:臨時表去重(需開啟 SELECT INTO)
SELECT DISTINCT * INTO #Tmp FROM TableName;
DELETE FROM TableName;
SELECT * INTO TableName FROM #Tmp;
DROP TABLE #Tmp;
-- 法二:保留最小 ID(單列去重)
DELETE FROM A WHERE UserID NOT IN (SELECT MIN(UserID) FROM A GROUP BY UserName, UserPwd);
-- 法三:EXISTS 相關子查詢(多列去重)
DELETE FROM Vitae a
WHERE (a.PeopleId, a.Seq) IN (
SELECT PeopleId, Seq FROM Vitae GROUP BY PeopleId, Seq HAVING COUNT(*) > 1
)
AND RowID NOT IN (SELECT MIN(RowID) FROM Vitae GROUP BY PeopleId, Seq HAVING COUNT(*) > 1);
-- 簡單恢復模式下
BACKUP LOG DBName WITH NO_LOG; -- 截斷非活動部分
BACKUP LOG DBName WITH TRUNCATE_ONLY; -- 兼容舊版本
DBCC SHRINKDATABASE(DBName); -- 釋放文件空間
-- 指定文件收縮(文件ID可從 sysfiles 查詢)
DBCC SHRINKFILE(1); -- 數據文件
DBCC SHRINKFILE(2); -- 日志文件
警告:生產環境嚴禁常態化收縮,會導致索引碎片爆炸、文件反覆增長。應設置合理自動增長、定期全量+日志備份、簡單恢復模式定期 CHECKPOINT。
| 需求 | T-SQL 寫法 | 示例輸出 |
|---|---|---|
| 完整日期時間 | CONVERT(VARCHAR, GETDATE(), 120) | 2024-05-18 14:30:25 |
| 僅日期 | CONVERT(VARCHAR(10), GETDATE(), 120) | 2024-05-18 |
| 年月 | CONVERT(VARCHAR(7), GETDATE(), 120) | 2024-05 |
| 時間 | CONVERT(VARCHAR(8), GETDATE(), 108) | 14:30:25 |
CHARINDEX('sub', col):查找子串位置(從1開始),不存在返回0。PATINDEX('%pattern%', col):支持通配符,可用於 TEXT 類型。STUFF(str, start, len, newStr):替換指定長度子串,常用於去首尾分隔符、拼接字符串。REPLACE(str, old, new):全量替換。REPLICATE(str, n):重複字符串。CREATE FUNCTION dbo.f_Go(@Start VARCHAR(10))
RETURNS @T TABLE (Path VARCHAR(MAX), 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;
-- 每類最新 N 條(按 ID 降序)
SELECT ClassCode = CASE WHEN EXISTS(SELECT 1 FROM T T1 WHERE T1.ClassCode = T.ClassCode AND T1.ClassID < T.ClassID) THEN '' ELSE T.ClassCode END,
ClassName
FROM T
ORDER BY ClassCode, ClassID DESC;
-- 分組拼接字符串(如貨位合併)
CREATE FUNCTION dbo.FC_Str(@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_Str(ProductID) AS Positions FROM TB GROUP BY ProductID;