怎么写 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 ) ,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 回复 - 其他回帖
-
-
有办法排除父任务已完成的子任务的吗?
只查询红框里的未完成的任务:
基于这一段 SQL 改:
SELECT * FROM blocks WHERE root_id = '20240402135122-f5usqip' AND type = 'i' AND markdown LIKE '* [ ]%' AND id not in (SELECT parent_id FROM blocks WHERE type = 'l' AND markdown LIKE '%* [ ]%') /* 该块[任务列表项块]的id 不是(未完成的任务列表块的父块[任务列表项块]id) [注:任务列表块的父块是上层任务列表项块] */
1 回复1 操作JeffreyChen 在 2024-05-31 13:39:35 更新了该回帖 - 查看全部回帖