正如@ Tomas-Takac所述，你不是需要使用#tmp1和删除，只需执行类似

  WITH  TempEmp（Id，grpid，pass） ，duplicateRecCount）
 AS  
（
 SELECT  Id，grpid，传递，
 ROW_NUMBER（） OVER （ PARTITION  通过 ID，grpid  ORDER   BY 传递） AS  duplicateRecCount 
 FROM  #tmp 
）
选择 Id，grpid，传递来自 TempEmp  WHERE  duplicateRecCount =  1  

但是你说你想使用Join 。一种方法是使用CTE的内容作为子查询。例如

  SELECT  A.Id，A.grpid，A.pass 
 FROM  #tmp A 
 INNER   JOIN （ SELECT  Id，grpid，pass，ROW_NUMBER（） OVER （ PARTITION  按 ID，grpid  ORDER   BY  pass）
 AS  duplicateRecCount  FROM  #tmp）
 as  B  ON  B.duplicateRecCount =  1   AND  A.Id = B.Id  AND  A.pass = B.pass 

Hi All,
I need your help for SQL. I have a table like;

ID TestID Status
1 2 PASS
1 2 FAIL
2 3 FAIL
2 3 PASS
3 4 FAIL
4 5 PASS

I want to return only one row for each ID. If ID has fail, return only record with fail so the outout should look like;

ID TestID Status
1 2 FAIL
2 3 FAIL
3 4 FAIL
4 5 PASS

What I have tried:

I have tried using CTE and Temp delete and its working fine but I wanted to do the same using Joins;

Create table #tmp
(
Id Int,
grpid int,
pass varchar(10)
)

Insert into #tmp (Id,grpid,pass)
Values (1,2,'Pass'),
(1,2,'Fail'),
(2,3,'Fail'),
(2,3,'Pass'),
(3,2,'Pass'),
(4,2,'Fail')

--Select * from #tmp

;WITH TempEmp (Id,grpid,pass,duplicateRecCount)
AS
(
SELECT Id,grpid,pass,ROW_NUMBER() OVER(PARTITION by Id,grpid ORDER BY pass)
AS duplicateRecCount
FROM #tmp
)
Select * into #tmp1 from TempEmp

Select * From #tmp1

--Now Delete Duplicate Records
DELETE FROM #tmp1
WHERE duplicateRecCount > 1

Select * From #tmp1

As @Tomas-Takac stated, you don't need to use #tmp1 and delete, just do something like

WITH TempEmp (Id,grpid,pass,duplicateRecCount)
 AS
 (
	SELECT Id,grpid,pass, 
	  ROW_NUMBER() OVER(PARTITION by Id,grpid ORDER BY pass) AS duplicateRecCount
	FROM #tmp
 )
 Select Id,grpid,pass from TempEmp WHERE duplicateRecCount = 1

But you said you wanted to use a Join. One way could be to use the contents of your CTE as a sub-query. e.g.

SELECT A.Id, A.grpid, A.pass
FROM #tmp A
INNER JOIN (SELECT Id,grpid,pass,ROW_NUMBER() OVER(PARTITION by Id,grpid ORDER BY pass) 
          AS duplicateRecCount FROM #tmp) 
   as B ON B.duplicateRecCount = 1 AND A.Id = B.Id AND A.pass = B.pass