SQL:取重复的行

SQL取重复的行有很多种方法，总结如下：

第一种exists：

select *  from zgda  a where exists
(select 1 from zgda b where a.cssj=b.cssj and a.rowid<>b.rowid )

第二种分析函数：
SELECT CSSJ , id
FROM
(
SELECT Count(*)  over(partition BY  CSSJ) n,CSSJ  ,id
FROM zgda )
WHERE  n > 1


select ID,CSSJ from (select a.*, row_number() over (partition by CSSJ order by CSSJ ) as row_num from zgda a)
where row_num > 1

第三种 自连接:
select a.*  from zgda  a inner join zgda b on  a.cssj=b.cssj and a.rowid!=b.rowid