将INNER JOIN与子查询一起使用以获取category_id为1和3的项目。子查询将选择category_id为1或3的所有item_id，主查询将与子查询在item_id上进行联接。

SELECT p1.item_id, p1.product_id, p1.category_id
FROM PRODUCT p1
INNER JOIN (
  SELECT item_id
  FROM PRODUCT
  WHERE category_id IN (1, 3)
  GROUP BY item_id
  HAVING COUNT(DISTINCT category_id) = 2
) p2 ON p1.item_id = p2.item_id
WHERE p1.category_id IN (1, 3);

样品：

-- create
CREATE TABLE PRODUCT (
  id INTEGER PRIMARY KEY,
  item_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  category_id INTEGER NOT NULL
);

-- insert
INSERT INTO PRODUCT VALUES (3862, 297,0, 1);
INSERT INTO PRODUCT VALUES (3861, 297,0, 3);
INSERT INTO PRODUCT VALUES (3860, 23, 0,1);

输出：

item_id product_id  category_id
297 0   3
297 0   1