基于不同关联条件在单SQL查询中生成两列的实现问题

现有表结构及数据 #

Table A #

id,key,roll_no
-------------
1,100,10
2,105|101,20

Table B #

key_1,key_2
----------
100,102
105,101

查询需求 #

生成两个新列：

• col_1：当A.key = B.key_1且A.roll_no = 10时，取A.id
• col_2：当A.key等于B.key_1与B.key_2以|拼接的字符串且A.roll_no = 20时，取A.id

现有单独查询语句 #

查询col_1 #

select A.id as Col_1
from TABLE_A A INNER JOIN  TABLE_B B
where A.key=B.key_1
and  A.roll_no=10

查询col_2 #

select A.id as Col_2
from TABLE_A A INNER JOIN TABLE_B B
where A.key=(concat(B.key_1,'|',B.key_2))
and  A.roll_no=20

合并后的查询语句 #

可以通过条件聚合实现同一条SQL生成两列，语句如下：

select
  max(case when A.key = B.key_1 and A.roll_no = 10 then A.id end) as col_1,
  max(case when A.key = concat(B.key_1, '|', B.key_2) and A.roll_no = 20 then A.id end) as col_2
from TABLE_A A
cross join TABLE_B B;

如果两张表存在明确关联逻辑，也可以用内关联配合条件判断：

select
  max(case when A.roll_no = 10 then A.id end) as col_1,
  max(case when A.roll_no = 20 then A.id end) as col_2
from TABLE_A A
join TABLE_B B 
  on (A.key = B.key_1 and A.roll_no = 10) 
  or (A.key = concat(B.key_1, '|', B.key_2) and A.roll_no = 20);

查询结果 #

执行后将得到期望输出：

col_1,col_2
-----------
1,2

内容的提问来源于stack exchange，提问作者Biswa Patra