【阿里健康】面试问题

Sql题目

有一张广告表：trace_ad_info，字段包含广告名称ad_name，广告图片pic_url，广告开始时间start_date，广告结束时间end_date，以及广告id ad_id；有一张广告接收规则表trace_ad_receiver_info，包含三个字段：广告id ad_id，接收类型receiver_type，接收id （receiver_id），这个表接收类型=1表示企业类型，接收类型=2表示区域；接收id：当接收类型是1的时候，接收id表示企业类型的值，当接收id是2是，表示广告投放的区域code； 还有一张广告黑白名单表 trace_black_white_ref，其中的列包含广告id ad_id，企业id ref_ent_id，以及类型（rec_type）；rec_type类型1表示白名单，只有在白名单中的企业可见，rec_type类型=2表示黑名单，黑名单的企业不可见； 问题：查询企业id=2，企业类型=1，所在区域code是430000在当前时间的所有广告列表，写出sql语句

思路分析

需要做多表的关联，同时注意排除黑名单的筛选（如果广告-企业id表里有记录，说明是配置了黑名单的企业，对于该企业广告是不可见的）

这个查询语句会返回trace_ad_info表中所有满足以下条件的广告：

广告的接收类型为1且接收ID为1，或者接收类型为2且接收ID为’430000’。

广告不在企业ID为2的黑名单中，或者在企业ID为2的白名单中。

广告的开始时间在当前时间之前，结束时间在当前时间之后。

解答

SELECT a.ad_name, a.pic_url, a.start_date, a.end_date, a.ad_id
FROM trace_ad_info a
LEFT JOIN trace_ad_receiver_info r ON a.ad_id = r.ad_id
LEFT OUTER JOIN trace_black_white_ref b ON a.ad_id = b.ad_id
WHERE 
    ((r.receiver_type = 1 AND r.receiver_id = 1) OR 
    (r.receiver_type = 2 AND r.receiver_id = '430000')) AND
    (b.ref_ent_id != 2 OR b.rec_type != 2) AND
    (b.ref_ent_id = 2 OR b.rec_type = 1) AND
    a.start_date <= NOW() AND a.end_date >= NOW();