查询以验证项目是否在分区组中 [英] Query to validate if items exist in partition group

问题描述

我有一组类似于以下格式的数据.

I have a set of data that resembles the following format.

GROUP | ELEMENT
---------------
  1   |   A
  1   |   B
  1   |   C
  2   |   A
  3   |   B
  4   |   A
  4   |   B
  5   |   A
  5   |   C

我希望能够验证A和B两个元素都存在于每个组中.理想情况下，我只返回同时具有这两个元素的那些组.在上面的示例中，我只想返回GROUP 1和GROUP 4.

I'd like to be able to verify that both elements A AND B exist in each of the groups. Ideally I would return only those groups that have both elements. In the example above I would like to return only GROUP 1 and GROUP 4.

抱歉，我不应该暗示"A"和"B"是唯一的选择.是否有可能专门寻找诸如"A"和"B"之类的特定值的存在?可能还有其他可能的值.我已经更新了示例数据以反映这一点.

Sorry I should have not implied that 'A' and 'B' were the only options. Is it possible to look specifically for the existence of specific values such as 'A' and 'B'? There may be other possible values. I have updated the example data to reflect this.

推荐答案

这更多是Relational Division问题，但您需要将每个GROUP与每个Element一起显示.

This is more of a Relational Division problem but you need to display each individual GROUP with each Element.

• 关系部门的SQL

查询:

SELECT  a.*
FROM    TableName a
WHERE   EXISTS
        (
            SELECT  1
            FROM    TableName b
            WHERE   a."GROUP" = b."GROUP" AND
                    b."ELEMENT" IN ('A','B')
            GROUP   BY b."GROUP"
            HAVING  COUNT(*) = 2
        )

• SQLFiddle演示
• SQLFiddle Demo

输出

╔═══════╦═════════╗
║ GROUP ║ ELEMENT ║
╠═══════╬═════════╣
║     1 ║ A       ║
║     1 ║ B       ║
║     1 ║ C       ║
║     4 ║ A       ║
║     4 ║ B       ║
╚═══════╩═════════╝

但是，如果您只想返回GROUP，则可以在下面简单地使用它

But if you want only to return GROUP, you can simply use this below

SELECT  "GROUP"
FROM    TableName b
WHERE   "ELEMENT" IN ('A','B')
GROUP   BY "GROUP"
HAVING  COUNT(*) = 2

• SQLFiddle演示
• SQLFiddle Demo

输出

╔═══════╗
║ GROUP ║
╠═══════╣
║     1 ║
║     4 ║
╚═══════╝

这篇关于查询以验证项目是否在分区组中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！