SQL 汇总

提取一个文档中的备注

SELECT 
    s.block_id,
    s.markdown AS span_memo_content,
    s.type AS span_type
FROM spans s
WHERE s.type = 'textmark inline-memo'
  AND s.root_id = '20250127072017-1l2f3ly';
​

‍

SELECT 
    -- 提取原文内容，去除括号中的内容，并用中文字段名"原文"
    SUBSTR(s.markdown, 1, INSTR(s.markdown, '（') - 1) AS 原文,
    -- 提取括号中的备注内容，并用中文字段名"想法"
    SUBSTR(s.markdown, INSTR(s.markdown, '（') + 1, INSTR(s.markdown, '）') - INSTR(s.markdown, '（') - 1) AS 想法
FROM spans s
WHERE s.type = 'textmark inline-memo'
  AND s.root_id = '20250127072017-1l2f3ly';
​

找到一个文档的所有反链

PRAGMA group_concat_max_len = 1000000;

SELECT 
    (SELECT hpath FROM blocks WHERE id = doc.id) AS __raw__反链文档路径,  -- 修正为 doc.id [^4]
    GROUP_CONCAT(b.markdown, '\n') AS __raw__完整文档内容  -- 简化拼接逻辑 [^1]
FROM refs
JOIN blocks doc ON refs.root_id = doc.id       -- 关联反链文档块 [^3]
JOIN blocks b ON doc.id = b.root_id            -- 主内容块
WHERE refs.def_block_root_id = '20250209143444-6wy4xx2'
AND doc.type = 'd'                             -- 文档类型过滤 [^4]
GROUP BY doc.id
ORDER BY doc.updated DESC;
​

获得所有没有正链的块

//!js
const query = async () => {
    let dv = Query.Dataview(protyle, item, top);
    let block = await Query.thisDoc(protyle);
    let blocks = await Query.sql(`
        select * from blocks as B
        where B.type='d' and box='${block.box}' and B.id not in (
            select distinct R.def_block_id from refs as R
        ) order by updated desc limit 1000`)
    blocks = await Query.fb2p(blocks);
    dv.addList(blocks, { type: 'o', columns: 2 });
    dv.render();
}
return query();
​

获得指定笔记本下的没有正链的块

使用嵌入块 ds

//!js
const query = async () => {
  let dv = Query.Dataview(protyle, item, top);
  //let block = await Query.thisDoc(protyle);
  let blocks = await Query.sql(`

select * from blocks 
where type='d' and box='20230414172155-2kwfbcn'
and id not in (
    select distinct refs.root_id 
    from refs 
    join blocks on blocks.id = refs.block_id
    where 
        blocks.box='20230414172155-2kwfbcn' 
        and refs.def_block_root_id != refs.root_id
)
order by updated desc
limit 1000

`)
  dv.addList(blocks, { type: 'o', columns: 2 });
  dv.render();
}
return query();
​

文档下面没有反链的文档

//!js
const query = async () => {
  let dv = Query.Dataview(protyle, item, top);
  const subchilds = await Query.childdoc('20241201094204-feckzs3');
  
  // 提取所有子文档的 root_id 并生成 SQL 条件
  const rootIds = subchilds.map(c => `'${c.root_id}'`).join(',');
  const rootCondition = rootIds ? `AND root_id IN (${rootIds})` : '';

  let blocks = await Query.sql(`
    SELECT * FROM blocks AS B
    WHERE type='d'
    ${rootCondition}
    AND id NOT IN (SELECT def_block_id FROM refs)
    ORDER BY updated DESC 
    LIMIT 128
  `);

  dv.addList(blocks, { type: 'o', columns: 2 });
  dv.render();
}
return query();
​

该查询将返回邮箱中没有引用“概念”笔记本下文档的文档

//!js
const query = async () => {
  let dv = Query.Dataview(protyle, item, top);
  //let block = await Query.thisDoc(protyle);
  let blocks = await Query.sql(`

select * from blocks
where type='d'
and box='20230414172155-2kwfbcn'
and id not in (
    select distinct refs.root_id from refs
    join blocks as def_doc on def_doc.id = refs.def_block_root_id
    where def_doc.box = '20250215152913-03hfgiz'
)
order by updated desc
limit 128

`)
  dv.addList(blocks, { type: 'o', columns: 2 });
  dv.render();
}
return query();
​

该查询将返回“邮箱”笔记本下没有被其他笔记(除了概念笔记以外的笔记)反链的文档

//!js
const query = async () => {
  let dv = Query.Dataview(protyle, item, top);
  //let block = await Query.thisDoc(protyle);
  let blocks = await Query.sql(`

select * from blocks 
where type='d' and box='20230414172155-2kwfbcn'
and id not in (
    select distinct refs.def_block_root_id from refs 
    where refs.box!='20250215152913-03hfgiz'
    and refs.root_id!=refs.def_block_root_id
)
order by updated desc
limit 1000

`)
  dv.addList(blocks, { type: 'o', columns: 2 });
  dv.render();
}
return query();
​

该查询将返回当前笔记本中，所有没有引用过其他笔记本（除了概念笔记本之外）

//!js
const query = async () => {
  let dv = Query.Dataview(protyle, item, top);
  //let block = await Query.thisDoc(protyle);
  let blocks = await Query.sql(`

select * from blocks 
where type='d' and box='20230414172155-2kwfbcn'
and id not in (
    select distinct refs.root_id 
    from refs 
    join blocks as def_doc 
        on refs.def_block_root_id = def_doc.id 
        and def_doc.box != '20250215152913-03hfgiz'  -- 被引用的文档不属于目标笔记本
    where refs.box='20230414172155-2kwfbcn'          -- 引用关系在当前笔记本
)
order by updated desc
limit 999

`)
  dv.addList(blocks, { type: 'o', columns: 2 });
  dv.render();
}
return query();
​

这个查询是找到概念中没有被其他文件引用的概念文件

//!js
const query = async () => {
  let dv = Query.Dataview(protyle, item, top);
  let blocks = await Query.sql(`
    SELECT * FROM blocks AS B
    WHERE type='d'
    AND box='20250215152913-03hfgiz'
    AND id NOT IN (SELECT def_block_id FROM refs)
    ORDER BY updated DESC 
    LIMIT 128
  `);

  dv.addList(blocks, { type: 'o', columns: 2 });
  dv.render();
}
return query();

​

所有文档

Query View@1.0.6 帮助文档.md

sy-query-view_README_zh_CN_md at main · frostime_sy-query-view.md

types.d.txt

思源 SQL 新人指南：SQL 语法 + Query + 模板 - 链滴.md

数据库表与字段 _ 思源社区文档.md

‍