sql查询去除重复值语句
sql 单表/多表查询去除重复记录
单表distinct
多表group by
group by 必须放在 order by 和 limit之前，不然会报错
************************************************************************************
1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
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)
3、查找表中多余的重复记录（多个字段）
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
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)
5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select * 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(*)>
❷ SQL查询，如何去除重复的记录
首先，先说明一个问题。这样的结果出现，说明系统设计是有问题的。
其次
删除重复数据，你要提供你是什么数据库。
不同数据库会有不同的解决方案。
关键字Distinct
去除重复，如下列SQL，去除Test相同的记录；
1.
select
distinct
Test
from
Table
2.
如果是要删除表中存在的重复记录，那就逻辑处理，如下：
3.
select
Test
from
Table
group
by
Test
having
count（test)>1
4.
先查询存在重复的数据，后面根据条件删除
还有一个更简单的方法可以尝试一下：
select
aid,
count(distinct
uid)
from
表名
group
by
aid
这是sqlserver
的写法。
如图一在数据表中有两个膀胱冲洗重复的记录。
2
可以通过sql语句“select
*from
表名
where
编码
in(select
编码
from
表名
group
by
编码
having
count(1)
>=
2)”来查询出变种所有重复的记录如图二
3
通过sql语句"
delete
from
表名
where
编码
in(select
编码
from
表名
group
by
编码
having
count(1)
>=
2)
and
编码
not
in
(select
max(编码)from
表名
group
by
编码
having
count(1)
>=2)
"来删除重复的记录只保留编码最大的记录
❸ SQL里面如何删除重复的记录
方法1 最简单的方法，拿出唯一的记录集，放入中间表。原表清空，再把数据导回来。数据少的话很快。
select distinct UPPER(Stu_ID),*from tableName
方法2 如果这个表特别大，导表的方法速度受不了的话：
找出所有重复记录
select * from tableName where Stu_ID_UP in (
select Stu_ID_UPfrom (select UPPER(Sut_ID) Stu_ID_UP from tableName)
group by Stu_ID_UP
having count(Stu_ID_UP) > 1）然后人工查看一下这些数据，推测这些重复数据产生的原因，是输入错误、代码逻辑错误、还是业务逻辑错误。要是业务原因，那一定要跟业务部门谈好解决方案，别删完出状况。
经过第2步的各种确认删除方法后，开始删~
保留rowid最小的记录
delete from tableName a
where (a.Stu_ID) in (
select Stu_ID_UP
from (
select UPPER(Sut_ID) Stu_ID_UP
from tableName)
group by Stu_ID_UP
having count(Stu_ID_UP) > 1)
and rowid not in (select min(rowid)
from (select UPPER(Sut_ID) Stu_ID_UP from tableName)
group by Stu_ID_UP
having count(Stu_ID_UP) > 1)
哎这恶心的排版，这网页里面真难调~~~
❹ SQL查询，如何去除重复的记录
sql查询去除重复值语句x0dx0asql 单表/多表查询去除重复记录x0dx0a单表distinctx0dx0ax0dx0a多表group byx0dx0ax0dx0agroup by 必须放在 order by 和 limit之前，不然会报错x0dx0ax0dx0a************************************************************************************x0dx0ax0dx0a1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断x0dx0ax0dx0aselect * from peoplex0dx0ax0dx0awhere peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)x0dx0a2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录x0dx0ax0dx0adelete from peoplex0dx0awhere peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)x0dx0aand rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)x0dx0a3、查找表中多余的重复记录（多个字段）x0dx0ax0dx0aselect * from vitae ax0dx0awhere (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)x0dx0a4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录x0dx0adelete from vitae ax0dx0awhere (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)x0dx0aand rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)x0dx0a5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录x0dx0ax0dx0aselect * from vitae ax0dx0awhere (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)x0dx0aand rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>
❺ sql查询去掉重复记录
1、打开要去掉重复数据的数据库，这里新建一张含有重复数据的user表做示例，如下图所示：
❻ sql中怎么删除两条重复记录并保留一条
将数据去重复后暂存到临时表#a中
selectdistinct*into#afromtable1where条件
deletetable1where删除限制条件
insertintotable1select*from#a-将暂存的数据插回数据库
droptable#a-删除临时表
注：当前的数据库，每一个表都应该有一个标志字段，以保证记录不完全重复，否则实用中极易出问题。
(6)sql删除相同记录扩展阅读：
SQL语句删除掉重复的其他情况
1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
SELECT
*
FROM
people
WHERE
peopleId IN (
SELECT
peopleId
FROM
people
GROUP BY
peopleId
HAVING
count(peopleId) > 1
)
2、查找表中多余的重复记录（多个字段）
SELECT
*
FROM
vitae a
WHERE
(a.peopleId, a.seq) IN (
SELECT
peopleId,
seq
FROM
vitae
GROUP BY
peopleId,
seq
HAVING
count(*) > 1
)
参考资料来源：结构化查询语言（SQL）-网络
❼ SQL删除重复
可新建一个临时表tmp来保存去重后的结果，具体mysql代码如下：
❽ sql中怎么将重复的记录去掉
方法一按照多条件重复处理：
delete tmp from(
select row_num = row_number() over(partition by 字段，字段 order by 时间 desc)
from 表 where 时间> getdate()-1
) tmp
where row_num > 1
方法二按照单一条件进行去重：
delete from 表 where 主键ID not in(
select max(主键ID) from 表 group by 需要去重的字段 having count(需要去重的字段)>=1
)
注意：为提高效率如上两个方法都可以使用临时表， not in 中的表可以先提取临时表#tmp，
然后采用not exists来执行，为避免数量过大，可批量用Top控制删除量
delete top(2) from 表
where not exists (select 主键ID
from #tmp where #tmp.主键ID=表.主键ID)
❾ SQL语句删除重复的记录
删除重复的数据
delete from tb where id not in (
select id from
(select fileSize,fileName ,max(id) id from tb group by filesize,filename ) a
)
现在完成了重复数据的删除，主要是利用了找出某个分组中最大的那个id，其中包括了所有不重复的id，然后使用not in将需要保留的排除。