两行具有相同ID但值不同的CASE

I have the following table. What I need is an sql query that will analyze two/more rows with the same id and: (1) CASE 'Result' is the same for both rows, print 'Passed'; (2) CASE 'Result' is not the same for both rows, print 'Failed'.

我有下表。我需要的是一个sql查询,它将分析具有相同id的两个/更多行,并且:(1)CASE'Result'对于两行都是相同的,print'Passed'; (2)CASE'结果'对于两行都不相同,打印'失败'。

ID  Result  
73  2000    Passed
73  2000    
43  2000    Failed
43  1003    

Is it all possible??

一切皆有可能吗?

3 个解决方案

#1

One option uses EXISTS:

一个选项使用EXISTS:

SELECT DISTINCT ID
FROM yourTable t1
WHERE NOT EXISTS (SELECT 1 FROM yourTable t2 WHERE t1.ID = t2.ID AND t2.Result <> t1.Result);

Another option uses aggregation:

另一个选项使用聚合:

SELECT ID
FROM yourTable
GROUP BY ID
HAVING COUNT(DISTINCT Result) = 1;

Demo

#2

If you want one row per id, then use group by:

如果你想要每个id一行,那么使用group by:

select id,
       (case when min(result) = max(result) then 'Passed'
             else 'Failed'
        end) as flag
from t
group by id;

If you want the value on all rows of your original table, then it is simple:

如果您想要原始表的所有行上的值,那么它很简单:

select id,
       (case when not exists (select 1
                              from t t2
                              where t2.result) = t.result
                             )
             then 'Passed' else 'Failed'
        end) as flag
from t
group by id;

However, putting the result on one row is problematic. There is no way to distinguish the rows, so it is more challenging (not impossible, but I suspect one of the above solves your real problem).

但是,将结果放在一行是有问题的。没有办法区分行,所以它更具挑战性(并非不可能,但我怀疑上面的一个解决了你的真正问题)。

#3

Try below:

select id, case when count (distinct result)=1 then 'Passed' else 'Failed' end
group by id