thinkyu/報到系統/SQL/SignUp序號管理工具.sql

-- =====================================================
-- SignUp 序號管理工具集
-- 提供多種場景的序號管理 SQL 腳本
-- =====================================================

-- =============================================================
-- 【場景1】初始化所有未設定序號的記錄
-- =============================================================
-- 執行此腳本可為 SeqNo = 0 或 NULL 的記錄自動補充序號

/*
DECLARE @UpdatedCount INT;

BEGIN TRANSACTION;

UPDATE SignUp
SET SeqNo = rn.RowNum
FROM SignUp su
INNER JOIN (
    SELECT 
        ID,
        ROW_NUMBER() OVER (PARTITION BY CourseID ORDER BY ID ASC) AS RowNum
    FROM SignUp
    WHERE SeqNo = 0 OR SeqNo IS NULL
) rn ON su.ID = rn.ID
WHERE su.SeqNo = 0 OR su.SeqNo IS NULL;

SET @UpdatedCount = @@ROWCOUNT;

COMMIT TRANSACTION;

SELECT CONCAT('成功更新 ', @UpdatedCount, ' 筆記錄的序號') AS [結果];
*/

-- =============================================================
-- 【場景2】重新編號特定活動的所有參加人員
-- 用途：當某個活動的序號亂掉時，重新編號
-- =============================================================

/*
DECLARE @CourseID INT = 1;  -- 修改為要重新編號的活動編號

BEGIN TRANSACTION;

UPDATE SignUp
SET SeqNo = rn.RowNum
FROM SignUp su
INNER JOIN (
    SELECT 
        ID,
        ROW_NUMBER() OVER (ORDER BY ID ASC) AS RowNum
    FROM SignUp
    WHERE CourseID = @CourseID
) rn ON su.ID = rn.ID
WHERE su.CourseID = @CourseID;

SELECT CONCAT('活動 ', @CourseID, ' 的序號已重新編號') AS [結果];

COMMIT TRANSACTION;
*/

-- =============================================================
-- 【場景3】檢查序號異常
-- =============================================================

-- 查看所有序號為 0 或 NULL 的記錄
SELECT 
    ID,
    CourseID,
    Name,
    Mobile,
    SeqNo
FROM SignUp
WHERE SeqNo = 0 OR SeqNo IS NULL
ORDER BY CourseID, ID;

-- 查看序號重複的記錄
SELECT 
    CourseID,
    SeqNo,
    COUNT(*) AS [重複數],
    STRING_AGG(CAST(ID AS VARCHAR), ',') AS [重複的ID]
FROM SignUp
WHERE SeqNo > 0
GROUP BY CourseID, SeqNo
HAVING COUNT(*) > 1
ORDER BY CourseID, SeqNo;

-- 查看序號不連續的活動
SELECT 
    CourseID,
    COUNT(*) AS [總筆數],
    MIN(SeqNo) AS [最小序號],
    MAX(SeqNo) AS [最大序號]
FROM SignUp
WHERE SeqNo > 0
GROUP BY CourseID
HAVING MAX(SeqNo) > COUNT(*)
ORDER BY CourseID;

-- =============================================================
-- 【場景4】統計各活動的序號分佈
-- =============================================================

SELECT 
    c.ID AS [活動編號],
    c.CourseName AS [活動名稱],
    COUNT(s.ID) AS [參加人數],
    CASE 
        WHEN COUNT(s.ID) = 0 THEN '無人員'
        WHEN MAX(s.SeqNo) = COUNT(s.ID) THEN '✓ 序號正常'
        ELSE '✗ 序號異常'
    END AS [序號狀態],
    MIN(s.SeqNo) AS [最小序號],
    MAX(s.SeqNo) AS [最大序號]
FROM Classes c
LEFT JOIN SignUp s ON c.ID = s.CourseID
GROUP BY c.ID, c.CourseName
ORDER BY c.ID;

-- =============================================================
-- 【場景5】修復所有序號異常
-- 說明：將所有 SeqNo <= 0 的記錄重新編號
-- 警告：這會修改所有未正確設定序號的記錄
-- =============================================================

/*
BEGIN TRANSACTION;

DECLARE @CourseID INT;
DECLARE @CurrentCourseID INT;
DECLARE @RowNum INT;

-- 建立暫時表存儲要更新的ID和新序號
CREATE TABLE #TempUpdate (
    ID INT,
    NewSeqNo INT
);

-- 為每個活動中 SeqNo <= 0 的記錄分配新序號
INSERT INTO #TempUpdate (ID, NewSeqNo)
SELECT 
    ID,
    ROW_NUMBER() OVER (PARTITION BY CourseID ORDER BY ID ASC) + 
    ISNULL((
        SELECT MAX(SeqNo) 
        FROM SignUp s2 
        WHERE s2.CourseID = s1.CourseID AND s1.SeqNo > 0
    ), 0) AS NewSeqNo
FROM SignUp s1
WHERE s1.SeqNo = 0 OR s1.SeqNo IS NULL;

-- 執行更新
UPDATE SignUp
SET SeqNo = tu.NewSeqNo
FROM SignUp su
INNER JOIN #TempUpdate tu ON su.ID = tu.ID;

-- 清理暫時表
DROP TABLE #TempUpdate;

SELECT '序號修復完成' AS [操作結果];

COMMIT TRANSACTION;
*/

-- =============================================================
-- 【場景6】數據備份檢查
-- 執行本腳本前，建議先備份資料
-- =============================================================

-- 查看 SignUp_BAK 表中最近的備份記錄
SELECT TOP 100
    ID,
    CourseID,
    SeqNo,
    Name,
    Mobile,
    DB_TRMOD,
    DB_TRDAT,
    DB_TRUSR
FROM SignUp_BAK
ORDER BY DB_TRDAT DESC;

-- 統計備份記錄數
SELECT 
    DB_TRMOD AS [操作類型],
    COUNT(*) AS [備份筆數],
    MAX(DB_TRDAT) AS [最近更新時間]
FROM SignUp_BAK
GROUP BY DB_TRMOD
ORDER BY MAX(DB_TRDAT) DESC;