select * from
(
select count(*) as cnt,col1,col2,..coln from Table
group by col1,col2,..coln
) a
where a.cnt>1cnt,col1,col2,..coln 为Table的所有列。
(
select count(*) as cnt,col1,col2,..coln from Table
group by col1,col2,..coln
) a
where a.cnt>1cnt,col1,col2,..coln 为Table的所有列。
)
group by 子句后跟的字段就是你用来判断重复的条件,如只有col1,那么只要col1字段内容相同即表示记录相同。
2,
没有ID的情况select identity(int,1,1) as id,* into #temp from tabel
select * from #temp where id not in (
select max(id) from # group by col1,col2,col3...)
TableID not in(select TableID from YouTable
where TableID in (select min(useid) from YouTable group by 重复字段))
select * from table where col1+','+col2+','...col5 in ( select max(col1+','+col2+','...col5) from table
where having count(*)>1
group by col1,col2,col3,col4
)
group by 子句后跟的字段就是你用来判断重复的条件,如只有col1,那么只要col1字段内容相同即表示记录相同。
select col1+','+col2+','...col5 from table
having count(*)>1
group by col1,col2,col3,col4
)
select * from table where col1+','+col2+','...col5 in ( select max(col1+','+col2+','...col5) from table
where having count(*)>1
group by col1,col2,col3,col4
)
group by 子句后跟的字段就是你用来判断重复的条件,如只有col1,那么只要col1字段内容相同即表示记录相同。
select * from YourTableName where (姓名 in (select 姓名 from YourTableName group by 姓名 HAVING (COUNT(*) > 1))) order by 姓名
其中YourTableName是你的要搜索的表名,将“姓名”换成你要过滤的重复字段名
select * from table where 主键 not in (
select min(主键) from table group by 其他字段的列表)也可以将min换为max
那就用一个临时表,人为加一个不重复的字段标识上去,再查询,当然这个方法不是太好:--生成带标识字段的临时表
select id=identity(int,1,1) * into #tb from 你的表--查询结果
select * from $tb where id not in (
select min(id) from #tb group by 其他字段的列表)--删除临时表
drop table #tb
group by col1, col2, ...
having count(*) > 1