id type flag
01 send a
01 send b
01 send c
02 send a
02 send b
01 rece a
01 rece b
结果为:
01 send a
02 send a
01 rece a
就是先按type字段分组,然后同一组中id相同的只取一条,sql 该怎么写
...全文
21319打赏收藏
去除重复记录的SQL
数据: id type flag 01 send a 01 send b 01 send c 02 send a 02 send b 01 rece a 01 rece b 结果为: 01 send a 02 send a 01 rece a 就是先按type字段分组,然后同一组中id相同的只取一条,sql 该怎么写
select * from 表 a
where not exists(select * from 表 where type=a.type and flag<a.flag)
--如果不满足上述条件,则用临时表
select mid=identity(int),* into #t from 表
select id,type,flag
from #t a
where not exists(select * from #t where type=a.type and mid<a.mid)
drop table #t
--生成测试数据
select '01' as id ,'send' as type, 'a' as flag into #t
union select '01','send','b'
union select '01','send','c'
union select '02','send','a'
union select '02','send','b'
union select '01','rece','a'
union select '01','rece','b'
--执行删除操作
delete a from #t a where a.flag != (select top 1 flag from #t where id = a.id and type = a.type order by flag)
--查询结果
select * from #t
--结果:
id type flag
----------------------
01 rece a
01 send a
02 send a
select * from 表 a
where not exists(select * from 表 where type=a.type and flag<a.flag)
--如果不满足上述条件,则用临时表
select mid=identity(int),* into #t from 表
select id,type,flag
from #t a
where not exists(select * from #t where type=a.type and mid<a.mid)
drop table #t
zjcxc(邹建) 老兄的,漏个条件
如果表中没有主键,或者是与id配合后不重复的字段
则用临时表生成一个主键
select mid=identity(int),* into #t from 表
select * from #t a where not exists(select * from #t where id=a.id and mid<a.mid AND type = a.type)
drop table #t