如何检查列中的所有值是相同的 [英] How to check all the values in a column is same

问题描述

大家好，我是Sql的新手。有人可以帮我解决下面的问题。

我需要选择Batchno，其状态字段值为'Finished'，如果有任何状态字段对Batchno的价值是'待定'，不应该选择它。任何帮助将不胜感激。

Hi all , I am new to Sql .Could someone help me in the below problem .
I need to select Batchno whose all status field value is 'Finished' ,if any of the status field value is 'pending' against the Batchno it should not be selected.Any help will be appreciated.

BatchNo  SID   Status 
2101     123    Finished
2101     457    Pending
2101     478    Finished
2101     8745   Finished
2102     4574   Finished
2102     4578   Finished
2102     4789   Finished
2103     6214   Finished
2103     4236   Pending
2103     7895   Pending                       

输出应为'2102'



什么我试过了：



SELECT Batchno FROM Product其中Status ='Finished'GROUP BY（Batchno） - 这会选择所有状态正在等待的batchno 。

The output should be '2102'

What I have tried:

SELECT Batchno FROM Product where Status='Finished' GROUP BY(Batchno) -This selects all the batchno whose status is pending also.

推荐答案

试试这个：

Try this:

select distinct BatchNo from TABLE as x
where 
((select count(*) Status from TABLE where BatchNo = x.BatchNo) = 
(select count(*) Status from TABLE where BatchNo= x.BatchNo and Status = x.Status ))

以下是查询..

Below is the query..

select distinct BatchNo from groupdata
where BatchNo not in (select BatchNo from groupdata where status = 'Pending')

从[UDB_JAX]中选择BatchNo。[dbo]。[test]为x

其中BatchNo不在
中（从[UDB_JAX]中选择BatchNo。[dbo] ] [测试] wh批次BatchNo = x.BatchNo和状态！='完成'）

group by BatchNo

select BatchNo from [UDB_JAX].[dbo].[test] as x
where BatchNo not in
(select BatchNo from [UDB_JAX].[dbo].[test] where BatchNo= x.BatchNo and Status != 'finished' )
group by BatchNo

这篇关于如何检查列中的所有值是相同的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！