如何计算列和同一组中的不同值

I have to distinguish different values in a group, how do I perform that? Example of my table:

我必须区分组中的不同值，我该如何执行？我的表示例：

Person  Status
1       Y
2       N
3       Y
3       N

-If a person only has Y status then display Yes

- 如果某人只有Y状态，则显示是

-If a person only has N status then display No

- 如果一个人只有N状态，则显示No

-If a person has both Y and N status then display No

- 如果一个人同时具有Y和N状态，则显示No.

Result:

结果：

Person  Status
1       Yes
2       No
3       No

How do I perform last logic when a person has both status? Here is the statement I tried which worked but I am not 100% sure if it is accurate:

当一个人同时具有两种状态时，如何执行最后一次逻辑？这是我尝试过的声明，但是如果它准确的话，我不是100％确定：

CASE WHEN SUM(CASE WHEN Status = 'Y' THEN 1 ELSE 0 END)>=1 AND SUM(CASE WHEN Status = 'N' THEN 1 ELSE 0 END)>=1 THEN 'No' 
    WHEN SUM(CASE WHEN Status  = 'Y' THEN 1 ELSE 0 END)>=1 THEN 'Yes'
ELSE 'No'END

2 个解决方案

#1

You can use MIN:

你可以使用MIN：

select CASE WHEN MIN(status) = 'Y' THEN 'Yes' ELSE 'No' END
, Person
from table
group by Person

#2

Checking your query

For simplicity let's say that:

为简单起见，我们说：

(Y) -> SUM(CASE WHEN Status = 'Y' THEN 1 ELSE 0 END)
(N) -> SUM(CASE WHEN Status = 'N' THEN 1 ELSE 0 END) 

So your query can be written as pseudocode:

所以你的查询可以写成伪代码：

case when (Y)>=1 and (N)>=1 then 'No'
     when (Y)>=1 then 'Yes'
     else 'No'
end

To check if your query is correct You need to build a truth table from your query for all possible input:

检查您的查询是否正确您需要从查询中为所有可能的输入构建一个真值表：

  (Y) | (N)      (Y)>=1 | (N)>=1      (Y)>=1 and (N)>=1      Result
 -----------    -----------------    -------------------    --------
   0  |  0        false | false             false             'No'
   0  | >=1   =>  false | true    =>        false        =>   'No'
  >=1 |  0        true  | false             false             'Yes'
  >=1 | >=1       true  | true              true              'No'

Look's like You got it right!

看起来就像你做对了！

Creating a correct condition

To be sure if You are making your condition correctly You need to build a truth table:

确定你的条件是否正确你需要建立一个真值表：

(1)
    Yes   |    No     | Result
-------------------------------
found     | found     | No
not found | found     | No
found     | not found | Yes
not found | not found | Null

or

要么

(2)
         \       No  
    Yes   \ Found | Not Found |
-------------------------------
 Found     |  No  |    Yes    |
-------------------------------
 Not Found |  No  |    Null   |
 ------------------------------

The first thing to notice is that if status==No is found the result is always No.

首先要注意的是，如果找到status == No，则结果始终为No.

Only when a No is not found and a Yes is found then we return Yes.

只有当找不到No并且找到Yes时，我们才返回Yes。

So Your condition can be written with a nested case.

所以你的病情可以用嵌套的情况写出来。

select columns, ....
(
  case when (SUM(CASE WHEN Status = 'N' THEN 1 ELSE 0 END)>=1) then 'No'
       else case when (SUM(CASE WHEN Status = 'Y' THEN 1 ELSE 0 END)>=1) then 'Yes'
                 else NULL
            end
  end
) as result

It's easy with 2 variables in a condition.
If You have more variables then I suggest You look into a Karnaugh map

在条件中使用2个变量很容易。如果您有更多变量，那么我建议您查看卡诺图

Checking the 'MIN' answer

Another answer here gave an original solution:

这里的另一个答案提供了原始解决方

select CASE WHEN MIN(status) = 'Y' THEN 'Yes' ELSE 'No' END
, Person
from table
group by Person

Is it correct?

这是对的吗？

Let's check first all the possible input.

让我们先检查所有可能的输入。

Input:
{Y} - contains only Y in the column
{N} - contains only N in the column
{Y,N} - contains Y and N in the column
{} - Y and N do not appear in the column

Let's calculate all the possible results:

让我们计算所有可能的结果：

Input        MIN(status)      Result
-------     -------------    --------
 {Y}             Y             Yes
 {N}     =>      N        =>   No
 {Y,N}           N             No
 {}             NULL           No

It's a correct answer.

这是一个正确的答案。