mysql - 仅显示购买特定产品而不购买其他特定产品的记录

Question

我有这个假设的表格

transac_id     user_id        product
----------     -------        -------
  2051613       189546      monthly plan
  8746169       189546      commission fee
  7845946       998741      commission fee
  8897155       166235      sms
  6325477       166235      newsletter
  8897452       166235      commission fee
  4328941       302604      monthly plan
  8897415       309888      sms
  2564718       960007      commission fee
  7451352       960007      yearly plan

我需要提取的是购买了佣金产品的user_id，而不是包月计划和包年plan，并且只会显示包含佣金产品的记录。基本上，它会显示:

 transac_id     user_id         product
 ----------     -------         -------
  7845946       998741      commission fee
  8897155       166235      commission fee.

谢谢!

Answer 1

要在多对多表中查找具有特定属性值为 a 的行但没有值为 b 的行的实体，您可以执行不存在的自连接。这是表与其自身的LEFT JOIN，其中联接条件指定相同的实体和值b。 (在LEFT JOIN中，如果左表中的一行在右表中没有对应的行，则该行中右表中的所有列都将为NULL。)然后，查询仅保留右表主键 IS NULL 的行，这种情况只有在此类不存在的行中才会发生。

SELECT cft.transac_id, cft.user_id, cft.product
FROM transactions cft
LEFT JOIN transactions myp
ON (cft.user_id = myp.user_id
    AND myp.product IN ('monthly plan', 'yearly plan'))
WHERE cft.product = 'commission fee'
  AND myp.transac_id IS NULL

(在 MySQL 中，如果表是TEMPORARY，则自联接需要使用 second copy of the data in another TEMPORARY TABLE 来解决 MySQL 锁中的已知缺陷。)