高分求一SQL语句

来源：百度知道 编辑：UC知道 时间：2024/06/18 23:58:02
用表admin里的name,sex,company三个字段判断记录是否重复，如果以上上个都一样的 就保留字段ID最的一条
数据库是SQL2000
To:w31org DISTINCT也能这样用呀，我有点晕了
To:roy_88 语法错误

--select
select * from admin a where not exists(select 1 from admin where name=a.Name and sex=a.sex and company=a.company and ID>a.ID)--最后一条

--delete
delete a from admin a where exists(select 1 from admin where name=a.Name and sex=a.sex and company=a.company and ID>a.ID)

select min(id),其他字段 from admin a ,
(select name,sex,company from admin group by name,sex,company) b
where a.name=b.name
and a.sex=b.sex
and a.company=b.company

df

select Distinct name,sex,company from admin order by name desc

select *
from admin
where id in
(select max(id) from admin group by name,sex,company)

先说你在什么数据库里执行？SQL 2000？ORACLE？ACCESS？字段ID最的一条
最什么的一条？
SQL 2000,保留ID最小一条：
delete from admin a where not exsits (select * from (select min(id) as newid,name,sex,company from admin group by name,sex,company)b where
a.id=b.newid and a.name=b.name and a.sex=b.sex and a.company =b.company)