思源笔记sql使用示例

1 数量过滤类

子块数量小于2的文档块(可能是空块)

select * 
from blocks 
where id in (
  select root_id 
  from blocks 
  group by root_id 
  having count(root_id )<=2
)

被引用数量超过6的块(超级节点)

select * 
from blocks 
where id in (
  select def_block_id 
  from refs 
  group by def_block_id 
  having count(def_block_id )>=6
)

引用其他文档数量超过6次的块(可能是moc)

select * 
from blocks 
where id in (
  select block_id 
  from refs 
  group by block_id
  having count(block_id )>=6
)

完全没有被引用的文档

select * 
from blocks 
where not id in (
  select root_id 
  from refs 
)
and type ='d'

2 时间类

一个月以上没有更新的文档(可能是死文档或者应当归档的文档)

select * 
from blocks 
where updated < strftime('%Y%m%d%H%M%S',datetime('now','-1 month'))  
and type ='d' 
or type ='doc'

3 特殊排序

按照内容模式排序

这里举个例子
假设有四个同事的简介文档




现在需要在一个地方文档里汇总这些同事的联系方式那些，但是想要去掉生日

select * 
    from blocks 
  where type = "h" 
  and root_id in (
    select id 
    from blocks 
    where content like "%同事%"
      ) 
  and content not like "%生日%"     
  order by 
  root_id, (
    case 
    when content = "姓名" then 0  
    when content = "电话" then 1  
    when content = "住址" then 2 
    end 
  )

结果是这样的