题外话:如果这个表一共只有2列,id列和name列,很好解决:
select distinct MIN(id) id,[name] from [t_people] group by [name]
下面开始正题:
如果这个表除主键id外,还有2至N个列,这N个列里面的数据都一样,则取id最小的那一行数据
如上图,id为:1和3、2和5的数据(除id外)是一样的,则过滤掉id为3和5这两条数据,显示其他数据行
方法一, 用 group by:
select MIN(id) id,[name],[age] from [t_people] group by [name],[age] order by id
方法二,用 having:
select * from [t_people]
where id not in (select max(id)[id] from [t_people] group by [name],[age] having count(*) >1)
方法三,用 临时表 #tmp (不推荐):
if OBJECT_ID(N'tempdb..#tmp',N'U') is not null
DROP TABLE #tmp;
select * into #tmp from [t_people]
delete from #tmp where id in (select max(id)[id] from #tmp group by [name],[age] having count(*) >1)
select * from #tmp order by id asc
DROP TABLE #tmp;