怎么写 SQL 能查询红色(无子项)排除蓝色(有子项)?
相关帖子
-
WITH RECURSIVE finish_tree AS ( -- 非递归部分:选择根分类 SELECT id, parent_id FROM blocks WHERE markdown like "* [x]%" and type = 'i' and subtype = 't' UNION ALL -- 递归部分:选择所有子分类 SELECT fi.id, fi.parent_id FROM blocks as fi INNER JOIN finish_tree as ft ON fi.parent_id = ft.id ) select * from blocks where id not in (select id from finish_tree) and root_id = '20240522181405-mdiudfl' and type = 'i' and subtype = 't' limit 10
3 回复 - 其他回帖
-
性能确实差,目前一次查询要半秒,放到数据库模板列之后更撑不住。
还有优化的方法吗?如果 SQL 没法优化的话,思源有办法通过什么方式优化吗?
我给每个查询都加上了
and root_id = '20240402135122-f5usqip'
,效率提升 2/5 。1 回复 -
这确实写的出来,但是提醒一下,太多子查询很慢的:
with RECURSIVE finish_tree AS ( -- 非递归部分:选择根分类 SELECT id, parent_id FROM blocks WHERE markdown like "* [x]%" and type = 'i' and subtype = 't' UNION ALL -- 递归部分:选择所有子分类 SELECT fi.id, fi.parent_id FROM blocks as fi INNER JOIN finish_tree as ft ON fi.parent_id = ft.id ) ,un_finish_parent AS ( SELECT id, parent_id FROM blocks WHERE type = 'l' and id in ( select parent_id from blocks where markdown like "* [ ]%" and type = 'i' and subtype = 't' ) ) select * from blocks where id not in (select parent_id from un_finish_parent ) and id not in (select id from finish_tree) and root_id = '20240522181405-mdiudfl' and type = 'i' and subtype = 't' limit 10
1 回复 - 查看全部回帖