时间:2021-05-02
例如:
id name value
1 a pp
2 a pp
3 b iii
4 b pp
5 b pp
6 c pp
7 c pp
8 c iii
id是主键
要求得到这样的结果
id name value
1 a pp
3 b iii
4 b pp
6 c pp
8 c iii
方法1
复制代码 代码如下:
delete YourTable
where [id] not in (
select max([id]) from YourTable
group by (name + value))
复制代码 代码如下:
delete a
from 表 a left join(
select id=min(id) from 表 group by name,value
)b on a.id=b.id
where b.id is null
查询及删除重复记录的SQL语句
查询及删除重复记录的SQL语句
1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
复制代码 代码如下:
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
复制代码 代码如下:
delete from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)
复制代码 代码如下:
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
复制代码 代码如下:
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
复制代码 代码如下:
声明:本页内容来源网络,仅供用户参考;我单位不保证亦不表示资料全面及准确无误,也不保证亦不表示这些资料为最新信息,如因任何原因,本网内容或者用户因倚赖本网内容造成任何损失或损害,我单位将不会负任何法律责任。如涉及版权问题,请提交至online#300.cn邮箱联系删除。
注:此处“重复”非完全重复,意为某字段数据重复 HZT表结构ID intTitle nvarchar(50)AddDate datetime 数据一.查找重
Oracle中查询某个表的总字段数,要用SQL语句,或者在PL/SQL里面复制代码代码如下:selectcount(column_name)fromuser_t
本文实例讲述了PHP查询并删除数据库多列重复数据的方法。分享给大家供大家参考,具体如下:$value){$sql_del="DELETEFROMfriendWH
重复的数据可能有这样两种情况,第一种:表中只有某些字段一样,第二种是两行记录完全一样。一、对于部分字段重复数据的删除1.查询重复的数据 select字段1,字
复制数据库表中两个字段数据的SQL语句今天为表新添加一个字段,但又想与表中的另一个字段值相同,由于数据过多想通过sql语句实现,经测试下面的这句话确实很好用。复