mysql - 如果一列不为空，如何排除具有相同 id 的所有行

Question

我的以下查询:

select oh.id_order,pc.id_product_comment,od.product_id
from `ps_order_history` oh
INNER JOIN `ps_orders` o ON o.id_order = oh.id_order
INNER JOIN `ps_order_detail` od ON od.id_order = oh.id_order
LEFT JOIN `ps_product_comment` pc ON pc.`id_customer` = o.`id_customer` AND pc.`id_product` = od.`product_id`
where oh.`id_order_state` = 2
AND (oh.date_add < pc.date_add OR pc.date_add IS NULL)

返回以下结果:

id_order  |  id_product_comment  |  product_id
----------------------------------------------
  6              3                 6
  6             NULL               5
  5             NULL               6
  5             NULL               5

我想排除 id_order 6 的行，因为其中一行的 id_product_comment 不为空。有什么想法吗？

Answer 1

好的解决方案:

    select oh.id_order,pc.id_product_comment,od.product_id
from `ps_order_history` oh
INNER JOIN `ps_orders` o ON o.id_order = oh.id_order
INNER JOIN `ps_order_detail` od ON od.id_order = oh.id_order
LEFT JOIN `ps_product_comment` pc ON pc.`id_customer` = o.`id_customer` AND pc.`id_product` = od.`product_id`
where oh.`id_order_state` = 2
AND (oh.date_add < pc.date_add OR pc.date_add IS NULL)
GROUP BY oh.id_order
HAVING pc.id_product_comment IS NULL