时间戳

首页 > 解决方案 > Return only single record if name is test

sql - Return only single record if name is test

问题描述

I have a table:

Declare @t table (ID int,name nvarchar(100))

Insert into @t values (1,'Test')
Insert into @t values (1,'A')
Insert into @t values (1,'B')
Insert into @t values (2,'R')
Insert into @t values (2,'S')
Insert into @t values (3,'T')

My requirement is to return only 1 record for the id which is having 'Test' as name:

My output will return:

ID  name
1   Test    
2   R   
2   S   
3   T

I tried this query:

select * from @t t
where exists (select 1 from @t t1 where t.ID=t1.ID and name ='test')

But no luck.

Can anyone please tell me what is the issue here?

标签： sqlsql-serversql-server-2008

解决方案

你可以这样做NOT EXISTS：

SELECT t.*
FROM @t t
WHERE t.name = 'Test'
OR NOT EXISTS (SELECT 1 FROM @t WHERE ID = t.ID AND name = 'Test')

或使用MAX()窗口功能：

SELECT ID, name
FROM (
  SELECT *, 
    MAX(CASE WHEN name = 'Test' THEN name END) OVER (PARTITION BY ID) test
  FROM @t
) t
WHERE test IS NULL OR name = test;

请参阅演示。
结果：

> ID | name
> -: | :---
>  1 | Test
>  2 | R   
>  2 | S   
>  3 | T

推荐阅读