方法2: delete from AAA as a where id not in (select min(id) from AAA where No=a.No and LC=a. LC )
楼主可以查询要删除结果 select * from AAA as a where not exists(select 1 from AAA where No=a.No and LC=a. LC and ID<a.id) select * from AAA as a where id not in (select min(id) from AAA where No=a.No and LC=a.LC )
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 )
不行呀!以上语句执行时均提示:在关键字 'As' 附近有语法错误。我想是“Delete From AAA As A行不通的原因。
加上别名 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 ta (ID int , [No] varchar(2) , LC smallint) insert ta 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 --drop table tadelete a--加上别名 from ta as a where exists(select 1 from ta where No=a.No and LC=a.LC and ID<a.id)select * from ta ID No LC ----------- ---- ------ 1 A 1 2 A 2 3 B 1 4 B 2(所影响的行数为 4 行)
上面把not exists 改为exists就行了 create table ta (ID int , [No] varchar(2) , LC smallint) insert ta 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 --drop table tadelete a--加上别名 from ta as a where id not in (select min(id) from ta where No=a.No and LC=a.LC )select * from ta ID No LC ----------- ---- ------ 1 A 1 2 A 2 3 B 1 4 B 2(所影响的行数为 4 行)
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', 2delete 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)
delete from AAA as a
where id not in (select min(id) from AAA where No=a.No and LC=a. LC )
select * from AAA as a
where not exists(select 1 from AAA where No=a.No and LC=a. LC and ID<a.id)
select * from AAA as a
where id not in (select min(id) from AAA where No=a.No and LC=a.LC )
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 )
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 )
insert ta 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 --drop table tadelete a--加上别名
from ta as a
where exists(select 1 from ta where No=a.No and LC=a.LC and ID<a.id)select * from ta
ID No LC
----------- ---- ------
1 A 1
2 A 2
3 B 1
4 B 2(所影响的行数为 4 行)
create table ta (ID int , [No] varchar(2) , LC smallint)
insert ta 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 --drop table tadelete a--加上别名
from ta as a
where id not in (select min(id) from ta where No=a.No and LC=a.LC )select * from ta
ID No LC
----------- ---- ------
1 A 1
2 A 2
3 B 1
4 B 2(所影响的行数为 4 行)
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', 2delete 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)