sql 删除重複資料，保留重複最小ID

select * from test      

ID

Name

1

a

2

b

3

a

4

b

5

c

6 d

-- --标準sql方法：mysql、sql server--
-- 查詢重複 方法一
select t.id,t.name from test t join(
  select min(id) id,name from test group by name having(count(1)>1)
) tt on t.id>tt.id and t.name=tt.name

-- 查詢重複 方法二
select * from test  where exists(
  select * from (
    select min(id) id,name from test group by name having(count(1)>1)
  ) as tt where test.id>tt.id and test.name=tt.name
)  

-- 删除重複 方法 
delete from test  where exists(
  select * from (
    select min(id) id,name from test group by name having(count(1)>1)
  ) as tt where test.id>tt.id and test.name=tt.name
)  

-- --sql server方法----
-- 查詢
select * from (
  select ROW_NUMBER() over(partition by name order by id ) row,id,name from test 
) t where t.row>1


-- 删除
delete from test  where exists(
  select * from 
  (
    select ROW_NUMBER() over(partition by name order by id ) row,id,name from test 
  ) t where t.row>1 and test.id=t.id
)      

select * from test      

ID

Name

1

a

2

b

5

c

6

d