mysql - 如何从一对多关系的 "many"表中选择具有两个特定子 ID 的父 ID？

Question

假设我有一张表格，其中列出了各种玩具及其使用的电池类型:

toy_id   battery_id    battery_qty
     1            3              1
     2            2              4
     2            3              1
     3            1              1

我想构建一个查询，告诉我哪些玩具可以使用 2 型和 3 型电池(但也可能使用其他类型)。在上面的示例中，它是 toy_id 2。

我该如何编写这样的查询？

这是我到目前为止的内容，但看起来很乱:

SELECT t1.toy_id,
    t1.battery_id b1, t2.battery_id b2,
    t1.battery_qty qty1, t2.battery_qty qty2
FROM toys t1
LEFT JOIN toys t2
    ON t1.toy_id = t2.toy_id
WHERE t1.battery_id = 2
    AND t2.battery_id = 3
HAVING q1 > 0
    AND q2 > 0
ORDER BY toy_id ASC;

结果看起来是正确的，但我很好奇我是否遗漏了什么。

Answer 1

我觉得join在这里不是很需要。你只需要计算记录的实例数，它等于你搜索的条件数。

SELECT  toy_ID
FROM    toys
WHERE   battery_ID IN (2,3)
GROUP BY toy_ID
HAVING COUNT(*) = 2

• SQLFiddle Demo

但如果没有为每个 toy_id 在 battery_id 上定义唯一约束，则需要在 HAVING 子句上使用 DISTINCT。

SELECT  toy_ID
FROM    toys
WHERE   battery_ID IN (2,3)
GROUP BY toy_ID
HAVING COUNT(DISTINCT battery_ID) = 2

• SQLFiddle Demo