sql 查询去除重复行

首先，从img表中取数据库，将new_id重复的过滤掉，代码为

select

min(id)

from

img

group

by

new_id

------以new_id字段分组，取最小的ID，这个ID总不会重复了吧

然后将这个查询结果以虚拟表形式，作为过滤条件，取你所要的结果，代码为

select

Tnew_id

AS

is,title,d_time,imgurl

from

news,Img

where

newsid

=

imgnew_id

and

imgid

in

(select

min(id)

AS

img_id,new_id

from

img

group

by

new_id)

希望这个例子对你有所帮助：

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 (apeopleId,aseq) in (select peopleId,seq from vitae group by peopleId,seq having

count() > 1)

4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录

delete from vitae a

where (apeopleId,aseq) 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 (apeopleId,aseq) 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)

(4)查询的值唯一有的时候查询的结果有许多行重复，这时可以使用DISTINCT语句来消除结果集中的重复行。比如查询所有客户的所在城市，由于可能存在同一个城市有好几个客户，这样选择出来的城市将会出现重复，使用DISTINCT语句就可以避免出现这种情况。比如下面的例子:select distinct Cityfrom Customer

select

字段1,字段2,字段3

from

table

group

by

字段1,字段2,字段3

having

count()>1

用上边这句能找出所有重复的数据

字段1，2，3你替换成你表里的字段名，如果有更多字段的话，你就继续添加，最后group

by的时候不要忘记了

删除的时候要建立一个临时表

create

table

new_table

as

select

字段1,字段2,字段3

from

old_table

group

by

字段1,字段2,字段3;

然后删除原表数据

truncate

table

old_table;

然后把临时表数据反插回去

insert

into

new_table

select

from

old_table;

以上就是关于sql 查询去除重复行全部的内容，包括:sql 查询去除重复行、SQL删除重复行、消除excel的sql查询结果集中的重复行等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！