如何仅选择在同一列中具有两个不同值的一行 [英] How do I select only one row that have two different values in same column

问题描述

大家好，

我需要你的SQL帮助。我有一张桌子;



ID TestID状态

1 2 PASS

1 2失败
2 3失败

2 3通过

3 4失败

4 5通过



我想为每个ID只返回一行。如果ID失败，只返回记录失败，所以outout应该是这样的;



ID TestID状态

1 2失败
2 3失败

3 4失败

4 5通过



什么我试过了：



我尝试过使用CTE和Temp删除它的工作正常但是我想用连接做同样的事情;



创建表#tmp

（

Id Int，

grpid int，
传递varchar（10）

）



插入#tmp（Id，grpid，pass）

值（1,2，'通过'），

（1,2，'失败'），

（2,3，'失败'） ，

（2,3，'通行证'），

（3,2，'通行证'），

（4,2， '失败'）



- 从#tmp中选择* / $


; WITH TempEmp（Id，grpid ，传递，duplicateRecCount）

AS

（

SELECT Id， grpid，pass，ROW_NUMBER（）OVER（由Id划分，grpid ORDER BY传递）

AS duplicateRecCount

FROM #tmp

）

从TempEmp中选择*进入＃tmp1



选择*从＃tmp1



- 现在删除重复记录

从＃tmp1删除

WHERE duplicateRecCount> 1



选择*来自#tmp1

解决方案

正如@ 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 （< span class =code-keyword> 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

这篇关于如何仅选择在同一列中具有两个不同值的一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！