如何基于advices表的advisor_type关联多表查询对应顾问姓名

你可以通过以下两种常用方式实现需求，不需要动态判断SELECT字段：

方法1：LEFT JOIN + COALESCE 关联查询 #

这是最推荐的写法，不会丢失advices表的任何记录，逻辑简洁易维护：

SELECT 
  a.id,
  a.advisor_id,
  a.advisor_type,
  COALESCE(e.lastname, f.lastname) AS lastname,
  COALESCE(e.firstname, f.firstname) AS firstname
FROM advices a
-- 仅当顾问类型为expert时关联expert表
LEFT JOIN expert e 
  ON a.advisor_id = e.id AND a.advisor_type = 'expert'
-- 仅当顾问类型为friend时关联friend表
LEFT JOIN friend f 
  ON a.advisor_id = f.id AND a.advisor_type = 'friend'

逻辑说明：关联时已经通过advisor_type过滤了不匹配的行，所以非对应类型的关联表字段都会返回NULL，COALESCE函数会自动取两个参数里第一个非空的值，正好匹配你要取对应类型名称的需求。

方法2：UNION ALL 分场景拼接结果 #

如果两类顾问的查询逻辑差异较大，也可以分开查询后拼接结果集：

-- 查询expert类型的记录
SELECT 
  a.id,
  a.advisor_id,
  a.advisor_type,
  e.lastname,
  e.firstname
FROM advices a
INNER JOIN expert e ON a.advisor_id = e.id
WHERE a.advisor_type = 'expert'

UNION ALL

-- 查询friend类型的记录
SELECT 
  a.id,
  a.advisor_id,
  a.advisor_type,
  f.lastname,
  f.firstname
FROM advices a
INNER JOIN friend f ON a.advisor_id = f.id
WHERE a.advisor_type = 'friend'
-- 按原advices的id排序，和原表顺序一致
ORDER BY id

注：你给出的期望结果中第4条记录的firstname为笔误，对应expert表id=8的firstname实际为Brad，上述SQL执行后会返回正确值。

内容的提问来源于stack exchange，提问作者Tom Kruk