查询按一列中的值进行分组，并确定第二列中的特定值是否在每个组中至少出现一次

This is related to the results I received in this question:

这与我在此问题中收到的结果有关：

Query which displays a table along with indications of which rows match another table

查询显示一个表以及哪些行与另一个表匹配的指示

With some query results or a table I'm trying to identify, for each Box id, if all instances of that Box id has a 'yes' in another column, or if there is at least one 'no'. All yes's would result in yes, any no's would result in no.

对于一些查询结果或我试图识别的表，对于每个Box id，如果该Box id的所有实例在另一列中都有'yes'，或者至少有一个'no'。所有的肯定会导致是，任何不会导致否。

例如

If this was a table:

如果这是一张桌子：

ID, Box, Match
1,Box100, yes
2,Box100, yes
3,Box100, yes
4,Box200, yes
5,Box200, no
6,Box200, yes
7,Box300, yes
8,Box300, yes
9,Box300, yes
10,Box400, no
11,Box400, no
12,Box400, yes

What would be the query to provide these results

提供这些结果的查询是什么

Box100, yes
Box200, no
Box300, yes
Box400, no

I tried to union two queries, one for the yes one for the no, using the UNIQUE statement to return only one, but that of course returns:

我尝试将两个查询联合起来，一个用于no的yes，使用UNIQUE语句只返回一个，但当然返回：

Box100, yes
Box200, yes
Box200, no
Box300, yes
Box400, yes
Box400, no

Which isn't what I am looking for.

这不是我要找的。

I also found this to be very difficult to determine what to call this, and therefore search for solutions, hence my perhaps convoluted title. So apologies in advance.

我还发现这很难确定要称之为什么，因此寻找解决方案，因此我可能是错综复杂的标题。所以请提前道歉。

I feel like the solution is probably pretty easy but I can't quite wrap my head around it.

我觉得解决方案可能很简单，但我无法完全理解它。

Thanks

谢谢

2 个解决方案

#1

Assuming no and yes are strings, you can simply use aggregation:

假设no和yes是字符串，你可以简单地使用聚合：

select box, min(match) as match
from t
group by box;

This works because of the alphabetical ordering of the values. A more general solution is:

这是因为值的字母顺序。更通用的解决方案是：

select box,
       (case when sum(case when match = 'no' then 1 else 0 end) > 0
             then 'no' else 'yes'
        end) as match
from t
group by box;

#2

Gordon's answer is what I would most likely use, however you could do it this way ....

戈登的回答是我最有可能使用的，但你可以这样做....

declare @example as table (
    exampleID int identity(1,1) not null primary key clustered
,   box       varchar(255) not null
,   match     varchar(25) not null
);

insert into @example (box, match) 

select 'Box100', 'yes' union all
select 'Box100', 'yes' union all
select 'Box100', 'yes' union all
select 'Box200', 'yes' union all
select 'Box200', 'no' union all
select 'Box200', 'yes' union all
select 'Box300', 'yes' union all
select 'Box300', 'yes' union all
select 'Box300', 'yes' union all
select 'Box400', 'no' union all
select 'Box400', 'no' union all
select 'Box400', 'yes';

    select distinct a.box
         , isnull(b.match, c.match) match
      from @example a
 left join @example b
        on a.box = b.box
       and b.match = 'no'
 left join @example c
        on a.box = c.box
       and c.match = 'yes'

Result Set:

结果集：

box     match
Box100  yes
Box200  no
Box300  yes
Box400  no

#1