使用分析函数:select pk1,pk2,pk3,code from ( select a.*,count(code) over (partition by pk1) cnt from a ) where cnt > 1
不好意思上面这个漏掉了一个分组条件select pk1,pk2,pk3,code from ( select a.*,count(*) over (partition by pk1,code) cnt from a ) where cnt > 1
就是要找到按某几個字段來說重復的數據: select pk1,code from a group by pk1,code having count(*)>1
不知道你的表是啥表,怎么会有三个primary key, 实际上在pk1上建立primary key,数据库自动会在上面建唯一索引,查询诸如pk1=?的数据时会利用唯一索引找出记录.这样已经是最高效率了. 所以直接查询就可以了. select * from a where pk1='1001'
select * from (select a.*,row_number() over(partition by pk1,code order by rownum) rm from a) where rk>1
select * from A where ( pk1, code ) in ( select pk1,code from A group by pk1,code having count(pk1)>1 )在Oracle中没问题,在SQL/Server中不支持(因SQL/Server支持多个字段用in)
(
select a.*,count(code) over (partition by pk1) cnt from a
)
where cnt > 1
(
select a.*,count(*) over (partition by pk1,code) cnt from a
)
where cnt > 1
select pk1,code from a group by pk1,code having count(*)>1
实际上在pk1上建立primary key,数据库自动会在上面建唯一索引,查询诸如pk1=?的数据时会利用唯一索引找出记录.这样已经是最高效率了.
所以直接查询就可以了.
select * from a where pk1='1001'
(select a.*,row_number() over(partition by pk1,code order by rownum) rm from a)
where rk>1
where ( pk1, code ) in
( select pk1,code from A group by pk1,code having count(pk1)>1 )在Oracle中没问题,在SQL/Server中不支持(因SQL/Server支持多个字段用in)