mysql - 我需要在此 SQL 指令中添加什么来过滤某些项目？

Question

我想要的是过滤(而不是获取)出现在这个新表中的报价，我们将其用作黑名单(即，对于域 X，我们不希望来源为 Y 的报价)。

TABLE:      black_list_deals

COLUMNS: id 
         domain_id (we save here an id from the table "domain")
         origin_offer (we save here a value from deal, the field deal_source_id)

直到现在我一直在使用这个指令，但现在我需要添加新的行为来根据黑名单表过滤这些商品。

SELECT
    a.lat,
    a.lon,
    a.id,
    a.image,
    a.link,
    a.deal_source_id,
    a.date_posted,
    a.date_expires,
    a.tags_external,
    a.tags_internal,
    a.gender,
    a.price,
    a.discount,
    a.areas,
    a.hits,
    a.bias,
    a.collection_type,
    a.uniqueness,
    a.date_posted = current_date AS today,
    DATEDIFF( a.date_expires, current_date) AS days_remaining,
    a.collection_type,
    b.id AS area_id,
    b.area,
    a.image_thumb,
    c.source_img_email,
    c.source_price,
    c.source_img_sm,
    c.source_name,
    c.frame,
    a.date_posted = current_date AS today,
    d.country,
    d.region,
    e.level1,
    e.level2,
    e.level3,
    e.level4

FROM deal a
      LEFT JOIN area b ON (a.areas = b.id)
      LEFT JOIN deal_source c ON (a.deal_source_id = c.id)
      LEFT JOIN deal_travel d ON (a.id = d.deal_id)
      LEFT JOIN deal_travel_country e ON (d.country = e.id)
WHERE
      a.validated = 'y' AND
      a.date_posted = current_date AND
      a.date_expires >= current_date AND
      email_deal = '1'

Answer 1

将 WHERE a.deal_source_id NOT IN (SELECT origin_offer FROM black_list_deals) 添加到您的 WHERE 子句应该可以解决这个问题。