怎么写 SQL 能查询红色(无子项)排除蓝色(有子项)?
相关帖子
- 其他回帖
-
-
这个需要用到递归,可能不是这么好理解:
WITH RECURSIVE finish_tree AS ( -- 非递归部分:选择根节点 SELECT id, parent_id FROM blocks WHERE markdown like "* [x]%" 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
1 回复 -
@zxhd86 我脑子已经烧掉了,这个 SQL 能做吗:
我用下面这个 SQL 会查询到 5 ,但查不到 6 :
(只能查询到 5 ,6 因为带有未完成的间接子任务所以被忽略了)
/* 排除父任务已完成的所有子任务id */ 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 root_id = '20240402135122-f5usqip' AND type = 'i' AND markdown LIKE '* [ ]%' AND id not in (select id from finish_tree) AND id not in (SELECT parent_id FROM blocks WHERE type = 'l' AND markdown LIKE '%* [ ]%') /* 该块[任务列表项块]的id 不是(未完成的任务列表块的父块[任务列表项块]id) [注:任务列表块的父块是上层任务列表项块] */
1 回复1 操作JeffreyChen 在 2024-05-31 20:47:37 更新了该回帖 - 查看全部回帖