当时级联是在Java代码里面写的,但是突然想通过sql的层面进行优化,可以利用case when
select
case when type3.`category_id` != '' then type3.`category_id`when type2.`category_id` != '' then type2.`category_id`
when type1.`category_id` != '' then type1.`category_id`
else '其他'
end category_id
from `goods_type` type3
left join `goods_type` type2 on type3.`parent_id` = type2.`id`
left join `goods_type` type1 on type2.`parent_id` = type1.`id`
where
type1.`is_disable` = 0 and type2.`is_disable` = 0 and type3.`is_disable` = 0
and type1.`status` = 2 and type2.`status` = 2 and type3.`status` = 2
and type3.`layer` = 3 and type2.`layer` = 2 and type1.`layer` = 1
and type3.`id` in
(select goods_type_id from `goods` goods
where goods.id = '00012C70-150E-4969-9137-F927B8ECEB8F')
case when then end 还可以用来做批量更新,批量删除,可以减少对数据库的连接操作,可以进行一次连接,然后操作多条数据
例如
update table
set name =
case id
when #{id1} then #{name1}
when #{id2} then #{name2}
....
end
where id in (#{id1}, #{id2},.....)