-- 查询重复记录的SQL语句集合
-- 适用于SQL Server数据库

-- 1. 查询stockin表中stockin_id重复的记录
-- 显示重复的stockin_id及其出现次数
SELECT 
    stockin_id,
    COUNT(*) as duplicate_count
FROM stockin 
GROUP BY stockin_id 
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;

-- 2. 查询stockin表中stockin_id重复的详细记录
-- 显示所有重复记录的完整信息
SELECT 
    s1.*
FROM stockin s1
INNER JOIN (
    SELECT stockin_id
    FROM stockin
    GROUP BY stockin_id
    HAVING COUNT(*) > 1
) s2 ON s1.stockin_id = s2.stockin_id
ORDER BY s1.stockin_id, s1.create_date DESC;

-- 3. 查询stockin表中除了最新记录外的重复记录（用于删除重复数据）
-- 基于create_date字段保留最新的记录
WITH RankedRecords AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY stockin_id ORDER BY create_date DESC) as rn
    FROM stockin
)
SELECT 
    stockin_guid,
    stockin_id,
    create_date,
    rn as record_rank
FROM RankedRecords
WHERE rn > 1  -- 显示除最新记录外的重复记录
ORDER BY stockin_id, create_date DESC;

-- 4. 通用模板：查询任意表的重复记录
-- 使用示例：将table_name和column_name替换为实际的表名和字段名
/*
SELECT 
    column_name,
    COUNT(*) as duplicate_count
FROM table_name 
GROUP BY column_name 
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;
*/

-- 5. 查询多个字段组合的重复记录
-- 示例：查询stockin表中stockin_id和asn组合重复的记录
SELECT 
    stockin_id,
    asn,
    COUNT(*) as duplicate_count
FROM stockin 
GROUP BY stockin_id, asn 
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;

-- 6. 删除重复记录的SQL（谨慎使用，建议先备份数据）
-- 保留最新的记录，删除旧的重复记录
/*
WITH RankedRecords AS (
    SELECT 
        stockin_guid,
        ROW_NUMBER() OVER (PARTITION BY stockin_id ORDER BY create_date DESC) as rn
    FROM stockin
)
DELETE FROM stockin 
WHERE stockin_guid IN (
    SELECT stockin_guid 
    FROM RankedRecords 
    WHERE rn > 1
);
*/

-- 注意事项：
-- 1. 在执行删除操作前，请务必备份数据
-- 2. 建议先在测试环境中验证SQL语句的正确性
-- 3. 可以根据实际业务需求调整排序字段和保留策略
-- 4. 对于大表，建议分批处理以避免锁表时间过长