题解 | #查询被投递过的职位信息#

##连接表
# select  
#     a. job_id as job_id, boss_id, company_id, post_time, salary, job_city
# from
#     job_info as a
# left join
#     deliver_record as b
# on
#     a.job_id = b.job_id; 临时表

##查看数大于0的公司id
# select 
#     company_id
# from        
#     临时表
# group by
#     company_id
# having
#     count(disinct user_id) > 0;

##组合
with 临时表 as(
select  
    a. job_id as job_id, boss_id, company_id, post_time, salary, job_city, b.user_id as user_id
from
    job_info as a
left join
    deliver_record as b
on
    a.job_id = b.job_id    
)
select  
     job_id as job_id, boss_id, company_id, post_time, salary, job_city
from
    job_info 
where
    company_id in (select 
                    company_id
                from        
                    临时表
                group by
                    company_id
                having
                    count(distinct user_id) > 0)
;