针对不同情况查询不同的表封装为一个字段
需求
现有一个表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 '%**%'
