针对不同情况查询不同的表封装为一个字段

针对不同情况查询不同的表封装为一个字段

需求

现有一个表A，我们要根据表A里的字段type的值来决定是关联查询表B还是表C或是表D里的某个字段。

实现

select 
(case A.type
    when 1 then B.name
    when 2 then CONCAT(C.name,E.name)
    when 3 then D.name
end) as name
from A
left join B on (A.xx_id = B.id and A.type = 1)
left join C on (A.xx_id = C.id and A.type = 2)
left join D on (A.xx_id = D.id and A.type = 3)
left join E on (C.xx_id = E.id and A.type = 2)

其中case类似我们代码里的的switch的功能。

进阶

对于我们上边自定义的字段name支持模糊查询

方法一：

select 
(case A.type
    when 1 then B.name
    when 2 then CONCAT(C.name,E.name)
    when 3 then D.name
end) as name
from A
left join B on (A.xx_id = B.id and A.type = 1)
left join C on (A.xx_id = C.id and A.type = 2)
left join D on (A.xx_id = D.id and A.type = 3)
left join E on (C.xx_id = E.id and A.type = 2)
where   (case A.type
    when 1 then B.name
    when 2 then CONCAT(C.name,E.name)
    when 3 then D.name
end) like '%***%'

方法二：

select * 
from 
    (select 
        (case A.type
            when 1 then B.name
            when 2 then CONCAT(C.name,E.name)
            when 3 then D.name
            end) as name
      from A
        left join B on (A.xx_id = B.id and A.type = 1)
        left join C on (A.xx_id = C.id and A.type = 2)
        left join D on (A.xx_id = D.id and A.type = 3)
        left join E on (C.xx_id = E.id and A.type = 2)) as Q
where Q.name like '%**%'