Mysql环境下select两表联合多条件查询语句怎么写？

我高估了自己作为弱鸡对MYSQL语言的理解能力，对表格进行了重新要求，如下图：

这行代码足足耗费了我500年的功力😓

SELECT
    b0.id,
    ifnull(b1.numb ,0 ) yonggongshu,
    ifnull(b2.numb,0 ) zichanshu,
    ifnull(b2.numb /  b1.numb,0) pinjun 
FROM
    ( SELECT id FROM book1 UNION SELECT id FROM book2 ) b0
    LEFT JOIN ( SELECT id, SUM( numb ) numb FROM book1 WHERE leibie = "中层" OR leibie = "临时工" GROUP BY id ) b1 ON b1.id = b0.id
    LEFT JOIN ( SELECT id, SUM( numb ) numb FROM book2 WHERE zichan = "门面" OR zichan = "房屋" GROUP BY id ) b2 ON b2.id = b0.id
    ORDER BY b0.id

mysql没有full join,可以使用以下方式

select nvl(a.id, b.id) id,
       nvl(a.numb, 0) yonggongshu,
       nvl(b.numb, 0) zichanshu,
       b.numb/a.numb pinjun
  from (select id,sum(numb) numb from book1 a where a.leibie in ('中层', '临时工') group by id) a
  left join (select id,sum(numb) numb from book2 b where b.zichan in ('房屋', '门面') group by id) b
    on a.id = b.id
        union
        select nvl(a.id, b.id) id,
       nvl(a.numb, 0) yonggongshu,
       nvl(b.numb, 0) zichanshu,
       b.numb/a.numb pinjun
  from (select id,sum(numb) numb from book1 a where a.leibie in ('中层', '临时工') group by id) a
  right join (select id,sum(numb) numb from book2 b where b.zichan in ('房屋', '门面') group by id) b
    on a.id = b.id

注意中间的union,达到去重的效果

1. 确定id
   select distinct id from book1 where leibie in ('中层', '临时工');

2. 汇总yonggongshu
   select id, sum(numb) as yonggongshu from book1 where leibie in ('中层', '临时工') group by id;

3. 汇总zichanshu
   select id, sum(numb) as zichanshu from book2 where zichan in ('房屋', '门面') and id in (select distinct id from book1 where leibie in ('中层', '临时工')) group by id;

4. 最后求平均值
   select a.id, a.yonggongshu, ifnull(b.zichanshu, 0), ifnull(b.zichanshu, 0) / a.yonggongshu as pinjun from
   (select id, sum(numb) as yonggongshu from book1 where leibie in ('中层', '临时工') group by id) a
   left join
   (select id, sum(numb) as zichanshu from book2 where zichan in ('房屋', '门面') and id in (select distinct id from book1 where leibie in ('中层', '临时工')) group by id) b on a.id = b.id order by a.id;

5. 还要汇总非中层和临时工
   select a.id, 0, 0 from book1 a where not exists (select 1 from book1 b where a.id = b.id and b.leibie in ('中层', '临时工'));

6. 最后汇总

select id, yonggongshu, zichan, pinjun from (
select a.id as id, 0 as yonggongshu, 0 as zichanshu, 0 as pinjun from book1 a where not exists (select 1 from book1 b where a.id = b.id and b.leibie in ('中层', '临时工'))
union
select a.id as id, a.yonggongshu as yonggongshu, ifnull(b.zichanshu, 0) as zichanshu, ifnull(b.zichanshu, 0) / a.yonggongshu as pinjun from
(select id, sum(numb) as yonggongshu from book1 where leibie in ('中层', '临时工') group by id) a
left join
(select id, sum(numb) as zichanshu from book2 where zichan in ('房屋', '门面') and id in (select distinct id from book1 where leibie in ('中层', '临时工')) group by id) b on a.id = b.id
) order by id;

好家伙，搁这实现公司业务呢