SQL 怎么排除有子项的列表？

有办法排除父任务已完成的子任务的吗？

只查询红框里的未完成的任务：

基于这一段 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) [注：任务列表块的父块是上层任务列表项块] */

这个需要用到递归，可能不是这么好理解：

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

这个只会查询到 6 ：（我没专门学过 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

没变化诶。

隔一个小时再试又可以了 😳

还需要排除一下 6 ：

奇怪，在我这边是正常的

我人傻了，一样的文档有不同的结果：

我重建索引之后就好了。估计是调整表格缩进的时候索引不对，也不知道怎么复现这个问题。

我突然发现 queryBlocks 好像用不了这个

是里面的双引号的问题，改成单引号就 OK 了。

关联 Issue #11603 · siyuan-note/siyuan

@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) [注：任务列表块的父块是上层任务列表项块] */

阿巴阿巴，没太看懂需求。

这样能理解吗：

这确实写的出来，但是提醒一下，太多子查询很慢的：

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

性能确实差，目前一次查询要半秒，放到数据库模板列之后更撑不住。

还有优化的方法吗？如果 SQL 没法优化的话，思源有办法通过什么方式优化吗？

我给每个查询都加上了 and root_id = '20240402135122-f5usqip' ，效率提升 2/5 。

思源的后端数据库要是丛 SQLite 切换到 DuckDB，估计性能就可以了。