SQL 大全

2025-04-01 | 0 浏览 | 0 评论

SQL大全

查询某一个子文档下面不含标签的文档

see https://ld246.com/article/1723590423117

SELECT * FROM blocks where type = 'd' and path like '%20240725203634-j3t72jv/%' and tag = '' and id not in (
    SELECT root_id FROM blocks where type <> 'd' and path like '%20240725203634-j3t72jv/%' and tag <> ''
) ORDER BY random() LIMIT 2

查询包含指定标签的文档

see https://ld246.com/article/1734338710962

SELECT * FROM blocks where type = 'd' and tag like '%#demo2#%' or id in (
    SELECT root_id FROM blocks where type <> 'd' and tag like '%#demo2#%'
) ORDER BY created desc;

查询网络图片或网络链接

SELECT * FROM blocks WHERE type not in ('c', 'query_embed') and markdown like '%![%](http%)%'
-- 如果链接格式可以用not排除，比如
SELECT * FROM blocks WHERE type not in ('c', 'query_embed') and markdown like '%[%](http%)%' and markdown not like '%![%](http%)%'
-- 或
-- 思源不支持正则
-- SELECT * FROM blocks WHERE type not in ('c', 'query_embed') and markdown REGEXP '!\[.*?\]\(https?:\/\/[^\)]*\)';

查询父文档下所有没有任何内容的子文档

‍‍```sql
select * from blocks where path like '%/20250128095334-2lz30ur%' and type = 'd' and root_id not in (
    select distinct root_id from blocks where path like '%/20250128095334-2lz30ur%' and type != 'd' and content != ''
)
‍‍```

如果不包含父文档本身，可以sql最后添加 and root_id != '20250128095334-2lz30ur'

查询包含指定关键词的链接

SELECT *
FROM blocks
WHERE markdown LIKE '%[%关键词%](%)%' and type REGEXP '^[htp]$';

查询所有链接

SELECT *
FROM blocks
WHERE markdown LIKE '%[%](%)%';

‍

思源