mysql删除一张表中的重复记录数_mysql – SQL：查找重复项的数量,添加的新值以及在同一个表中删除的值(动态)...

这是我的看法.基于SQL Server

SELECT snapshot_date = COALESCE(c.snapshot_date, DATEADD(day, 1, p.snapshot_date)),

[count] = COUNT(c.snapshot_date),

dup_from_prev_day = SUM(CASE WHEN c.snapshot_date is not null

AND p.snapshot_date is not null

THEN 1 END),

sum_of_id_added = SUM(CASE WHEN c.snapshot_date is not null

AND p.snapshot_date is null

THEN 1 END),

sum_of_id_removed = SUM(CASE WHEN c.snapshot_date is null

AND p.snapshot_date is not null

THEN 1 END)

FROM yourTable c -- current

FULL OUTER JOIN yourTable p -- previous

ON c.snapshot_date = DATEADD(DAY, 1, p.snapshot_date)

AND c.uniqueID = p.uniqueID

GROUP BY COALESCE(c.snapshot_date, DATEADD(DAY, 1, p.snapshot_date))

HAVING COUNT(c.snapshot_date) > 0

/* RESULT :

snapshot_date count dup_from_prev_day sum_of_id_added sum_of_id_removed

2018-08-15 5 NULL 5 NULL

2018-08-16 7 3 4 2

2018-08-17 6 2 4 5

*/