有一个数据库表,表里有id,名称,年龄三个字段,然后插入了几条数据
1. 查询出名字重复的id
2. 对于那些名字重复的id,只保留最小的id,其它的id全部删掉表如下:
id name age
1 a 20
2 b 22
3 c 12
2 d 22
3 e 23
1 c 34
3 b 24请问,这两条语句怎么写??
1. 查询出名字重复的id
2. 对于那些名字重复的id,只保留最小的id,其它的id全部删掉表如下:
id name age
1 a 20
2 b 22
3 c 12
2 d 22
3 e 23
1 c 34
3 b 24请问,这两条语句怎么写??
select *
from tb
where name in(select name from tb group by name having count(*)>1);
2.
delete tb
where name in(select name from tb group by name having count(*)>1)
and id not in(select min(id) from tb
where name in(select name from tb group by name having count(*)>1)
group by name );
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY name ORDER BY ID) AS RN,A.* FROM A)
WHERE RN>1
2.DELETE FROM A
WHERE ID IN
(SELECT id FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY name ORDER BY ID) AS RN,A.* FROM A)
WHERE RN>1)