SQL查询中如何剔除重复

Question

例如：select username, date from user_info
where date>=to_date('2008-8-1 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and date<=to_date('2008-8-25 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
group by username, date
查询结果中同一username的date有多个，我只想保留一个，应该怎么修改语句？谢谢！

Answer 1

1，存在两条完全相同的纪录

这是最简单的一种情况，用关键字distinct就可以去掉

example： select distinct * from table(表名) where (条件)

2，存在部分字段相同的纪录（有主键id即唯一键）

如果是这种情况的话用distinct是过滤不了的，这就要用到主键id的唯一性特点及group by分组

example:

select * from table where id in (select max(id) from table group by [去除重复的字段名列表,....])

3，没有唯一键ID

example:

select identity(int1,1) as id,* into newtable(临时表) from table

select * from newtable where id in (select max(id) from newtable group by [去除重复的字段名列表,....])

drop table newtable

扩展资料

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)

参考资料：百度百科 结构化查询语言

Answer 2

1、存在部分字段相同的纪录

如果是这种情况的话用distinct是过滤不了的，这就要用到主键id的唯一性特点及group

代码：select * from table where id in (select max(id) from table group by [去除重复的字段名列表,....])

2、存在两条完全相同的记录

这是最简单的一种情况，用关键字distinct就可以去掉

代码：select distinct * from table(表名) where (条件)

3、没有唯一键ID

这种较为复杂

代码：

select identity(int1,1) as id,* into newtable(临时表) from table（原表）

select * from newtable where id in (select max(id) from newtable group by [去除重复的字段名列表,....])

drop table newtable

扩展资料：

SQL查询语句

1、查询全部的重复信息

select * from people where id not in (

select min(id) from people group by name,sex HAVING COUNT(*) < 2)

2、查询多余的重复信息

select * from people where id not in (

select MIN(id) from people group by name,sex)

本回答被网友采纳

Answer 3

关键字Distinct 去除重复

如下列SQL，去除Test相同的记录；

select distinct Test from Table

如果是要删除表中存在的重复记录，那就逻辑处理，如下：

select Test from Table group by Test having count（test)>1

先查询存在重复的数据，后面根据条件删除

Answer 4

如果结果中同一username的date有多个，按照username, date分组，用distinct 是没有效果的。 可以去掉按date分组，如：
select username, max(date) from user_info
where date>=to_date('2008-8-1 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and date<=to_date('2008-8-25 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
group by username本回答被提问者采纳

Answer 5

select distinct username, date from user_info
where date>=to_date('2008-8-1 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and date<=to_date('2008-8-25 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
group by username, date