我只想显示那些所有状态要么成功要么失败的ClaimID

Question

状态失败空空空

ClaimID	类别ID
1	成功
1	b	空
1		c	失败
2	成功
2		b	成功
2	c
3	a
3	b
3	c

你能试试下面的吗

SELECT T.ClaimID
FROM YourTable AS T
WHERE NOT EXISTS
(
SELECT 1 FROM YourTable AS T2 WHERE T.CliamID=T2.ClaimID AND T2.Status IS NULL
)

您可以GROUP BY ClaimID并在HAVING子句中设置条件：

SELECT ClaimID
FROM tablename 
GROUP BY ClaimID
HAVING COUNT(*) = COUNT(Status)  -- there are not any nulls

请参阅演示
结果：

> | ClaimID |
> | ------: |
> |       2 |

如果我理解你的问题，下面的查询应该可以

create table #YourTable  (ClaimID INT, CategoryID VARCHAR(10) ,[Status]VARCHAR(10))
INSERT INTO #YourTable VALUES
(1,'a', 'success'),(1,'b', null),(1,'c', 'fail'),(2,'a', 'success'),
(2,'b', 'success'),(2,'c', 'fail'),(3,'a', null),(3,'b', null),(3,'c', null)
SELECT
ClaimID,
CategoryID,
[Status]
FROM #YourTable
WHERE [Status] IN ('success','fail')
DROP TABLE #YourTable

输出

ClaimID CategoryID  Status
1         a         success
1         c         fail
2         a         success
2         b         success
2         c         fail