萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL 實用技巧大全:去重、縮縮日志、日期轉換、遞歸查詢、分組統計

1. 刪除重複記錄三板斧

-- 法一:臨時表去重(需開啟 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);

2. 事務日志收縮與數據庫瘦身

-- 簡單恢復模式下
BACKUP LOG DBName WITH NO_LOG;          -- 截斷非活動部分
BACKUP LOG DBName WITH TRUNCATE_ONLY;   -- 兼容舊版本
DBCC SHRINKDATABASE(DBName);            -- 釋放文件空間

-- 指定文件收縮(文件ID可從 sysfiles 查詢)
DBCC SHRINKFILE(1);  -- 數據文件
DBCC SHRINKFILE(2);  -- 日志文件

警告:生產環境嚴禁常態化收縮,會導致索引碎片爆炸、文件反覆增長。應設置合理自動增長、定期全量+日志備份、簡單恢復模式定期 CHECKPOINT。

3. 日期時間格式化速查表

需求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

4. 字符串處理常用函數

  • CHARINDEX('sub', col):查找子串位置(從1開始),不存在返回0。
  • PATINDEX('%pattern%', col):支持通配符,可用於 TEXT 類型。
  • STUFF(str, start, len, newStr):替換指定長度子串,常用於去首尾分隔符、拼接字符串。
  • REPLACE(str, old, new):全量替換。
  • REPLICATE(str, n):重複字符串。

5. 遞歸 CTE 查找路徑(圖遍歷)

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;

6. 分組取 Top N 並統計

-- 每類最新 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;
copyright © 萬盛學電腦網 all rights reserved