找出自身重复的行总结.-优快云博客

本文介绍了一个SQL查询实例，旨在找到表格中col,col2值相同且col3值为'D'的重复行。通过多种方法实现，包括使用ROW_NUMBER()、GROUP BY、HAVING、COUNT()等聚合函数，以及子查询来筛选并展示这些重复记录。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--要求找出col,col2 值相同并且col3 值为'D' 的重复行

  use TempTest 
  go   
  if OBJECT_ID ('Tb1') is not null drop table Tb1 
  Create Table Tb1  (
  colKey varchar(10) not null primary key ,
  col1 varchar(10) null,
  col2 varchar (10)  null
  ,col3 varchar (10)  null
  )
go


Insert into Tb1 
Select  'key1' , 'aa1' , 'C' , 'D'  union all 
Select  'key2' , 'aa2' , 'C' , 'D'  union all 
Select  'key3' , 'aa3' , 'D' , 'D'  union all 
Select  'key4' , 'aa4' , 'D' , 'D'  union all 
Select  'key5' , 'aa5' , 'C' , 'C'  union all 
Select  'key6' , 'aa6' , 'D' , 'C'  union all 
Select  'key7' , 'aa3' , 'D' , 'D'  union all 
Select  'key8' , 'aa4' , 'D' , 'C'  union all 
Select  'key9' , 'aa3' , 'D' , 'C'  union all 
Select  'key10' , 'aa10' , 'C' , 'D'  union all 
Select  'key11' , 'aa3' , 'D' , 'C'  union all 
Select  'key12' , 'aa12' , 'C' , 'D' 



SELECT * FROM [TempTest].[dbo].[Tb1] order by col1,col2,col3





------------------ 方法一-------------------------------------------------------------------------------

 
WITH a1 AS
( 
SELECT *,ROW_NUMBER()  OVER(PARTITION BY col1,col2 ORDER BY GETDATE()) re
FROM Tb1 
WHERE col3='D'
)
,a2 AS
(
SELECT col1,col2
FROM a1
GROUP BY col1,col2
HAVING MAX(re)>1
)
SELECT a.*
FROM a1 a
JOIN a2 b ON a.col1=b.col1 AND a.col2=b.col2
ORDER BY col1,col2,col3


------------------ 方法二------------------------------------------------------------------
select a.* from tb1 a inner join (
Select  col1, col2, col3, count(0) as sl from tb1
where  col3 = 'D'
group by col1, col2, col3
having count(0)  > 1)  b on a.col1 = b.col1 and a.col2 = b.col2 and a.col3 = b.col3 


--------------方法三--------------------------------------------------------------------------
---不正确
---SELECT *  FROM TB1 AS T WHERE  EXISTS(SELECT 1 FROM TB1 WHERE COL1=T.COL1 AND COL2=T.COL2  AND colKey<>T.colKey) AND COL3='D'

---此句正确
SELECT *  FROM TB1 AS T WHERE  EXISTS(SELECT 1 FROM TB1 WHERE COL1=T.COL1 AND COL2=T.COL2  AND colKey<>T.colKey AND COL3='D') AND COL3='D'

--------------方法四--------------------------------------------------------------------------

SELECT * FROM(
    SELECT *,COUNT(1)OVER( PARTITION BY col1,col2 ) C FROM tb1
    WHERE col3='D'
)T WHERE C>=2 order by col1,col2,col3 
 
 
 
--------------方法五--------------------------------------------------------------------------
 
select * from Tb1 as a where exists(select 1 from Tb1 where col1=a.col1 and col2=a.col2 and col3=a.col3 and col3='D' 
group by col1,col2 having COUNT(*)>1)