思源笔记丨 SQL 查询未完成的父任务

思源笔记的 sql 查询有一炮三响的问题，

只用下面这个 sql 查询未完成的任务，会重复显示子任务。

select * from blocks as B1 
where B1.root_id = '你的文档id' 
  and B1.type = 'i' 
  and B1.subtype = 't' 
  and B1.markdown like '* [ ]%'
​

所以让 Claude 写了一个只筛选未完成的顶级父任务的代码

代码

select * from blocks as B1 
where B1.root_id = '你的文档id' 
  and B1.type = 'i' 
  and B1.subtype = 't' 
  and B1.markdown like '* [ ]%'
  and (select B4.subtype from blocks as B4 
       where B4.id = (select B3.parent_id from blocks as B3 
                      where B3.id = B1.parent_id)) != 't'
​

效果

代码由 Claude 3.7 Sonnet 生成