mysql 选择是否存在一对多

Question

如何检查多对一关系是否已经存在？

SampleTable
╔════╦═══════╗
║ ID ║ OB_ID ║
╠════╬═══════╣
║  1 ║     1 ║
║  1 ║     2 ║
║  2 ║     3 ║
║  2 ║     1 ║
║  2 ║     2 ║
║  3 ║     1 ║
║  3 ║     3 ║
╚════╩═══════╝

我想搜索 SampleTable.ob_id 并选择 SampleTable.id(如果存在)。

搜索 (1,2)
返回1；

╔════╗
║ ID ║
╠════╣
║  1 ║
╚════╝

搜索(1,2,3)
返回2；

╔════╗
║ ID ║
╠════╣
║  2 ║
╚════╝

搜索 (2,3)
返回空记录集(或 null)

╔════╗
║ ID ║
╚════╝

Answer 1

SELECT ID
FROM tableName a
WHERE ob_id IN (1,2,3)
GROUP BY ID
HAVING COUNT(*) =
            (
              SELECT COUNT(*)
              FROM TableName b
              WHERE b.ID = a.ID
            ) AND
      COUNT(*) = 3 -- the value of this depend on the number of values
                   -- you have supplied on you where clause.

• SQLFiddle Demo for values [1, 2]
• SQLFiddle Demo for values [1, 2, 3]
• SQLFiddle Demo for values [2, 3]

如果没有为每个ID的ob_ID定义唯一约束，则需要使用DISTINCT

SELECT ID
FROM tableName a
WHERE ob_id IN (1,2)
GROUP BY ID
HAVING COUNT(DISTINCT ob_id) =
            (
              SELECT COUNT(DISTINCT ob_id)
              FROM TableName b
              WHERE b.ID = a.ID
            ) AND
      COUNT(DISTINCT ob_id) = 2