88 lines
2.3 KiB
SQL
88 lines
2.3 KiB
SQL
-- 查询重复记录的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. 对于大表，建议分批处理以避免锁表时间过长 |