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