sql技巧：两表关联不同情况关联不同字段

环境：MYSQL

问题描述：表A和表B关联，关联字段有cat1、cat2、cat3三个维度；表A是配置表，三个字段肯定有值，表B是事实表，三个字段不一定都有值，但是如果cat2有，则cat1肯定有，以此类推；

需求：将表A和表B关联，如果表B的cat3为空，则用cat1和cat2字段关联，如果cat2为空，则用cat1关联，以此类推。

use mytest;
-- 数据准备
create table mycat01 (
	cat1 varchar(10),
    cat2 varchar(10),
    cat3 varchar(10),
    val1 int
);

create table mycat02 (
	cat1 varchar(10),
    cat2 varchar(10),
    cat3 varchar(10),
    val1 int
);


insert into mycat01
values('100', '100-100', '100-100-1',22);
insert into mycat01
values('100', '100-200', '100-200-1',33);
insert into mycat01
values('200', '200-100', '200-100-1',44);


insert into mycat02
values('100', '100-100', '100-100-1',22);
insert into mycat02
values('100', '100-200', null,66);
insert into mycat02
values('200', null, null,88);

-- 直接关联：会出现缺失
select a.*, b.cat1 
from mycat01 a 
left join mycat02 b on a.cat1=b.cat1 and a.cat2=b.cat2 and a.cat3=b.cat3;

-- 解法1：写多个left join； 优点：可读性强；缺点：重复性高，left join次数多执行效率低。
select  a.*, b1.*, b2.*, b3.* 
from mycat02 a 
left join  mycat01 b1 on a.cat1 is not null and a.cat2 is not null and a.cat3 is not null
	and a.cat1=b1.cat1 and a.cat2=b1.cat2 and a.cat3=b1.cat3
left join  mycat01 b2 on a.cat1 is not null and a.cat2 is not null and a.cat3 is null
	and a.cat1=b2.cat1 and a.cat2=b2.cat2 -- and a.cat3=b.cat3
left join  mycat01 b3 on a.cat1 is not null and a.cat2 is null and a.cat3 is null
	and a.cat1=b3.cat1; -- and a.cat2=b.cat2 and a.cat3=b.cat3;


-- 解法2：将cat1-3拼起来，然后用like关联。
-- 把mycat01当做主表 
select a.*, b.cat_full, b.val1
from (select concat(cat1, '_', cat2, '_', cat3) as cat_full, val1 from mycat01) a 
left join (
	select concat(cat1, '_'
			, if(coalesce(cat2,'')<>'', concat(cat2, '_'),'')
			, coalesce(cat3,'')
        ) as cat_full
        , val1 
	from mycat02
) b on a.cat_full like concat(b.cat_full,'%'); -- 也可以用 locate(b.cat_full,a.cat_full)=1

-- 如果 mycat02是主表，left join左右对换也同样适用 
select b.*, a.cat_full, a.val1
from (
	select concat(cat1, '_'
			, if(coalesce(cat2,''), concat(cat2, '_'),'')
			, coalesce(cat3,'')
        ) as cat_full
        , val1 
	from mycat02
) a 
left join (
	select concat(cat1, '_', cat2, '_', cat3) as cat_full, val1 from mycat01
) b on b.cat_full like concat(a.cat_full,'%');

补充： 如果在Hive里，就不支持join...on里用like做关联，改用locate()，具体见上。