[Help]如何用最简单的方法删除重复的记录
SQL Server 2000
AAA表
ID（主索引，唯一） , No（varchar） , LC（smallint）
1 A 1
2 A 2
3 B 1
4 B 2
5 B 2 （以上一条记录重复，要删除）
......
我查过相关资料，感觉有点复杂，不知道谁有最简单的方法一次过删除重复的记录呢？
最终结果如下图：
1 A 1
2 A 2
3 B 1
4 B 2
......
谢谢！
[解决办法]
select * from AAA as a--把表名AAA转换为别名a
where not exists(select 1 from AAA where No=a.No and LC=a.LC and ID <a.id)
--a. LC这里多了一个空格去掉
delete a--加上别名
from AAA as a
where not exists(select 1 from AAA where No=a.No and LC=a.LC and ID <a.id)
delete a--加上别名
from AAA as a
where id not in (select min(id) from AAA where No=a.No and LC=a. LC )
[解决办法]
select id,nod,lc from aaa where id in (select min(id) as id from AAA group by nod,lc) order by id
[解决办法]
create table AAA(ID int, No varchar(10), LC smallint)
insert AAA select 1, 'A ', 1
union all select 2, 'A ', 2
union all select 3, 'B ', 1
union all select 4, 'B ', 2
union all select 5, 'B ', 2
delete AAA
where ID not in(
select min(ID) from AAA group by NO, LC
)
select * from AAA
--result
ID No LC
----------- ---------- ------
1 A 1
2 A 2
3 B 1
4 B 2
(4 row(s) affected)
