delete from bse_user a where exists (select 1 from (select min(user_id)userid,name from bse_user group by name) b where a.user_id=b.user_id and a.name=b.name)
delete a from (select *,row_number()over(partition by Name order by User_ID desc) as Row from Bse_User) as a where Row>1SQL2005以上版本可以这样用
delete Bse_User where User_Id in(select MIN(User_Id) from Bse_User group by Name having count(*)>1)
select MIN(User_Id) from Bse_User group by Name having count(*)>1这句话中,去掉MIN(),就有错误,我也不明白为什么。 select (User_Id) from Bse_User group by Name having count(*)>1
看来你没明白max/min/count/avg/sum这五个的用法和sql查询的顺序, select (User_Id) from Bse_User group by Name having count(*)>1这句,先from,在group by,再select,再having,group by只出现了name,那么你在select的之后只能出现name,而其他字段要用上面五个函数的其中一个或一些来包住,不然肯定报错。
where exists (select 1 from (select min(user_id)userid,name from bse_user group by name) b
where a.user_id=b.user_id and a.name=b.name)
from (select *,row_number()over(partition by Name order by User_ID desc) as Row from Bse_User) as a
where Row>1SQL2005以上版本可以这样用
select (User_Id) from Bse_User group by Name having count(*)>1
select (User_Id) from Bse_User group by Name having count(*)>1这句,先from,在group by,再select,再having,group by只出现了name,那么你在select的之后只能出现name,而其他字段要用上面五个函数的其中一个或一些来包住,不然肯定报错。
中的顺序是不是有误呀,应该是from,在group by,再having,再select的吧,请指教。