MYSQL Select Join - 在连接上指定唯一值

Question

SELECT T2.ITEM_ID AS item_ITEM_ID 
FROM types T 
  JOIN items T2
    ON T.ITEM_ID = T2.ITEM_PARENT_ID
WHERE T.ITEM_TYPE = 'I' 
 AND T2.ITEM_TYPE = 'I' 

此查询从 T2 返回具有重复 T2.ITEM_PARENT_ID 的行。
如何指定此查询仅返回具有唯一 T2.ITEM_PARENT_ID 的行？

Answer 1

计算重复次数，当重复次数超过 1 时将其过滤掉。

SELECT T2.ITEM_ID AS item_ITEM_ID, COUNT(*) dups
FROM types T 
  JOIN items T2
    ON T.ITEM_ID = T2.ITEM_PARENT_ID
WHERE T.ITEM_TYPE = 'I' 
 AND T2.ITEM_TYPE = 'I' 
GROUP BY item_ITEM_ID
HAVING dups = 1

DEMO