oracle 查某一列有重复值的记录

-- 查找重复记录
select names,num
from test
where rowid != (select max(rowid)
                 from test b
                where b.names = test.names and
                      b.num = test.num)

或者使用

select names,num
from test
where rownum!= (select max(rownum)
                 from test b
                where b.names = test.names and
                      b.num = test.num)

 

 对于sql server 的使用可能没有oracle 那么方便

如下：

declare @table table(
  id nchar(20),
  name nchar(10),
  number int
  )
  insert into @table  select id,name, row_number() over(order by id)  number from userapp
  --select *from @table
   select a.id,a.name
from @table a
where number!= (select max(number)
                 from @table b
                where b.id = a.id and
                      b.name = a.name)

代码是使用表变量进行的处理