如何根据其他两列的不同组合检查第三列中的匹配值? [英] How do you check for matching value in third column based on distinct combinations of other two columns?

问题描述

我有一个带有建筑物名称的表，例如A，B，C.这些建筑物名称可以重复.我还有另一列有发言权.例如第1层，第2层.同样，每个建筑物可能有多个第1层.第三列显示对象，例如电视，砖块，风扇. 我要检查建筑物与相应楼层的每种组合，例如A楼-1楼，A楼-2楼，如果存在对象砖"，则必须存在墙".

I have a table with Building name, such as A, B, C. These building names could be repeated. I have another column which has the floor. Such as floor 1 ,floor 2. Again there could be multiple floor 1 for every building. There is a third column which has object present such as television, bricks, fans. I want to check for every combination of building with corresponding floors, such as Building A - floor 1, Building A- floor 2, if an object 'brick' exists then 'wall' must exist.

示例数据: 对于每个建筑物和位置，如果都存在"WALL"，"WINDOW"，"WINDOW1"或"WINDOW2"，则其他标志

EXAMPLE DATA: For every building and location, if 'WALL' exists , 'WINDOW', 'WINDOW1' or 'WINDOW2' must exist, ELSE FLAG

BUILDING  LOCATION  OBJECT   
A         FLOOR1    WALL
A         FLOOR1    WINDOW  
A         FLOOR2    WALL  
B         FLOOR1    WALL  
C         FLOOR1    WALL  
C         FLOOR1    WINDOW

期望的输出

BUILDING  LOCATION  ACTION  

A         FLOOR2    FLAG
B         FLOOR1    FLAG

我尝试使用GROUP BY，DISTINCT，WHERE EXISTS，但是我似乎无法提出正确的逻辑.

I have tried using GROUP BY, DISTINCT, WHERE EXISTS, but I cant seem to come up with the right logic.

推荐答案

您可以group by building, location表示where object in ('WALL', 'WINDOW')行:

select building, location, 'FLAG' action
from tablename
where object in ('WALL', 'WINDOW')
group by building, location
having count(distinct object) < 2

having子句中的条件count(distinct object) < 2返回building, location的组合，其中'WALL'和'WINDOW'都不同时存在.
请参见演示.
结果:

The condition count(distinct object) < 2 in the having clause returns combination of building, location where 'WALL' and 'WINDOW' do not both exist.
See the demo.
Results:

| building | location | action |
| -------- | -------- | ------ |
| A        | FLOOR2   | FLAG   |
| B        | FLOOR1   | FLAG   |

或不存在:

select t.building, t.location, 'FLAG' action
from tablename t
where object in ('WALL', 'WINDOW')
and not exists (
  select 1 from tablename
  where building = t.building and location = t.location and object <> t.object
)

请参见演示.

这篇关于如何根据其他两列的不同组合检查第三列中的匹配值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！