SQL Server随机删除重复记录.txt

drop table test;
--先求出符合条件的记录：
create table test(col1 int identity(1,1), col2 varchar(10), col3 varchar(10));

insert into test(col2, col3)
select '我要银子','我没有' UNION ALL
select '我要银子','你有没？' UNION ALL
select '我要金子','金子涨价了' UNION ALL
select '多要银元','我没有' UNION ALL
select '我要银子','我没有' UNION ALL
select '我要银子', '你有没？' UNION ALL
select '我要银子', '明年才有' UNION ALL
select '我要银子', '你有没？'

select * from test;
select distinct col2, col2 from test;

select t1.all_sum as '所有记录行',
       t2.dis_sum as '无重复记录行',
       t1.all_sum-t2.dis_sum as '将要删除的行数'
from (select count(*) as all_sum from test ) t1,
(select count(*) as dis_sum from (select distinct col2, col3 from test) t) t2;

---如：随机删除 col2='我要银子'  的重复记录
select sum(row_sum) as all_del from (
select col2, col3, count(col1)-1 as 'row_sum'
 from test
where col2='我要银子'
group by col2, col3
having count(col1)-1<>0) t1

--删除条件：将col2、col3都相同的，视为重复记录，将其随机删除只剩一行
--选择语句:
select top (select sum(row_sum) as all_del from (
             select col2, col3, count(col1)-1 as 'row_sum'
            from test
            where col2='我要银子'
            group by col2, col3
            having count(col1)-1<>0) t1)
    t1.col1, t1.col2, t1.col3
    from test t1 where t1.col2='我要银子' and t1.col1 not in
  (select col1 from test t3 where exists (
                     select col2, col3
                     from test where col2='我要银子'
                     and col2=t3.col2 and col3=t3.col3
                     group by col2, col3 having count(col1)=1 ))
order by NEWID()

--删除语句:
DELETE FROM test
WHERE col1 in ( select col1 from (
select top (select sum(row_sum) as all_del from (
             select col2, col3, count(col1)-1 as 'row_sum'
            from test
            where col2='我要银子'
            group by col2, col3
            having count(col1)-1<>0) t1)
    t1.col1, t1.col2, t1.col3
    from test t1 where t1.col2='我要银子' and t1.col1 not in
  (select col1 from test t3 where exists (
                     select col2, col3
                     from test where col2='我要银子'
                     and col2=t3.col2 and col3=t3.col3
                     group by col2, col3 having count(col1)=1 ))
order by NEWID()) t
)