不常用SQL记录

小半 • 2023年1月15日下午9:20 • 技术随笔 • 阅读 266

目标一级二级菜单放到同一列表展示，搜索同时可搜索到一二级，若搜索到一级展示一级下的所有二级（表格中业务部）。没有二级就展示自己（表格中人事部）。
涉及一个问题点查询没有二级的一级，且表时用的逻辑删除。
效果图如下：

一级菜单	二级菜单
业务部	业务部小张
业务部	业务部小王
人事部

sql：

select s.* from table_name p,table_name s 
where p.id = s.pid
and ((p.is_deleted = '0' and s.is_deleted = '0'and p.name like :name) or (s.is_deleted = '0' and s.name like :name and s.pid<>0)) 
union
# 查询所有 没有子类型的类型
# 分俩步  1、left join 右表连接不到数据的（右连接都是null的）。2、右表连接到的数据都是已删除的 idDeleted
# select p.* ,s.*
select p.* 
from table_name p left join table_name s
on p.id = s.pid
# 有问题只要一个子类型已删除就查询到了
# where p.pid=0 and (s.id is null or s.is_deleted = '1') and p.is_deleted = '0' and p.name like :name
# 所有连接不到右表的数据
where p.pid=0 and s.id is null  and p.is_deleted = '0' and p.name like :name
union 

select p.* 
from table_name p left join table_name s
on p.id = s.pid
# 用count=sum 实现所有子类型都是 已删除逻辑
where p.pid=0 and p.is_deleted = '0' and p.name like :name 
GROUP BY s.pid having count(s.id) = sum(s.is_deleted)
# s.pid 和 p.id 是一个统计维度 都一样
# GROUP BY p.id having count(s.id) = sum(s.is_deleted)
order by create_time desc

Left Jion 右表没有数据导致总结果减少

select f.id f_table f LEFT JOIN r_table r on r.f_id=f.id where f.del=0 and r.del=0 and xxxx 因为where 直接加了r表的条件所有关联不到就满足不了r.del=0然后就比哪位了inner join。修改为select f.id f_table f LEFT JOIN r_table r on r.f_id=f.id and r.del=0 where f.del=0 and xxxx 主要是在on语句里加右表的条件