表TC_ACCOUNT中 ACCOUNT_NAME中有重复记录,如何删除这些重复记录保留ID最大的一个信息?
SELECT *
FROM tc_account
WHERE (account_name IN
(SELECT account_name
FROM tc_account
GROUP BY account_name
HAVING COUNT(account_name) > 1))
SELECT *
FROM tc_account
WHERE (account_name IN
(SELECT account_name
FROM tc_account
GROUP BY account_name
HAVING COUNT(account_name) > 1))
WHERE (account_name IN
(SELECT account_name
FROM tc_account
GROUP BY account_name
HAVING COUNT(account_name) > 1))
and id not in (select min(id) from tc_account group by account_name having count(tc_account)>1)
go
create table tb(id int,name varchar(10))
insert tb select
1,'A' UNION ALL SELECT
2,'A' UNION ALL SELECT
3,'B' UNION ALL SELECT
4,'B' UNION ALL SELECT
5,'B' UNION ALL SELECT
6,'C' UNION ALL SELECT
7,'C' DELETE TB
FROM TB T
WHERE EXISTS(SELECT 1 FROM TB WHERE NAME=T.NAME AND ID>T.ID)SELECT * FROM TB
id name
----------- ----------
2 A
5 B
7 C(所影响的行数为 3 行)
-------------- Author: Luoyoumou -----------------------------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都相同的,视为重复记录,将其随机删除指定条件(col2='我要银子')的重复记录,只剩一行
--选择语句:
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()
--删除语句: 将其随机删除(col2='我要银子'的重复记录)只剩一行
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
)
--删除语句: 将其随机删除所有重复的记录,每组重复记录只剩一行
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
group by col2, col3
having count(col1)-1<>0) t1)
t1.col1, t1.col2, t1.col3
from test t1 where t1.col1 not in
(select col1 from test t3 where exists (
select col2, col3
from test where col2=t3.col2 and col3=t3.col3
group by col2, col3 having count(col1)=1 ))
order by NEWID()) t
)
SELECT *
FROM
tc_account a,
(SELECT account_name
FROM tc_account
GROUP BY account_name
HAVING COUNT(account_name) > 1) b
WHERE
a.account_name=b.account_name
and
not exists(select 1 from tc_account where account_name=b.account_name and account_name=a.account_name and id>a.id)