登录 注册

求助 SQL 使用两个 LEFT JOIN 时报错

先设置一个简单的场景：

image.png

存在三个标题块，Test1 的备注(memo)中存着 Test2 的 ID，Test2 的备注中存着 Test3 的 ID。现在提供 Test1 的 ID，想通过关联，用 SQL 把 Test1、Test2、Test3 的 content 都显示出来。

只使用一个 LEFT JOIN，没问题

如果使用一个 LEFT JOIN 链接 Test1 和 Test2，再嵌套一个搜索显示 Test3 是没问题的。

SELECT
    Block1.content AS content1,
    Block2.content AS content2,
    (SELECT content FROM blocks WHERE id = Block2.memo) AS content3
FROM (
    SELECT
        memo,
        content
    FROM blocks
    WHERE id = '20250312162657-u184k20'
) AS Block1
LEFT JOIN (
    SELECT id, content, memo
    FROM blocks
) AS Block2 ON Block2.id = Block1.memo

image.png

使用两个 LEFT JOIN,就会报错

现在为了工整，像把 Test2 到 Test3 的嵌套搜索也改为用 LEFT JOIN 连接，此时就会报错。

SELECT
    Block1.content AS content1,
    Block2.content AS content2,
    Block3.content AS content3
FROM (
    SELECT
        memo,
        content
    FROM blocks
    WHERE id = '20250312162657-u184k20'
) AS Block1
LEFT JOIN (
    SELECT id, content, memo
    FROM blocks
) AS Block2 ON Block2.id = Block1.memo
LEFT JOIN (
    SELECT id, content
    FROM blocks
) AS Block3 ON Block3.id = Block2.memo;

报错信息为：near "ON":syntax error，应该是语法上的错误

是什么原因呢？

  • 思源笔记

    思源笔记是一款隐私优先的个人知识管理系统，支持完全离线使用，同时也支持端到端加密同步。

    融合块、大纲和双向链接，重构你的思维。

    24687 引用 • 101337 回帖 • 1 关注
  • Q&A

    提问之前请先看《提问的智慧》，好的问题比好的答案更有价值。

    9343 引用 • 42559 回帖 • 114 关注
深圳 位置 3 回帖
87 54 3 30

相关帖子

被采纳的回答
  • wilsons • • 1 赞同
    付费者 捐赠者

    思源中不可以，但使用其他客户端工具查询是可以的。

    可能是低版本的 sqlite 不支持链式 join，改进方式：用括号明确优先级

    这样可以

    SELECT
    Block1.content AS content1,
    Block2.content AS content2,
    Block3.content AS content3
FROM ((
    SELECT
        memo,
        content
    FROM blocks
    WHERE id = '20250312162657-u184k20'
) AS Block1
LEFT JOIN (
    SELECT id, content, memo
    FROM blocks
) AS Block2 ON Block2.id = Block1.memo)
LEFT JOIN (
    SELECT id, content
    FROM blocks
) AS Block3 ON Block3.id = Block2.memo;

    但建议简化成这样，性能更优

    SELECT
    b1.content AS content1,
    b2.content AS content2,
    b3.content AS content3
FROM (blocks b1
LEFT JOIN blocks b2 ON b2.id = b1.memo)
LEFT JOIN blocks b3 ON b3.id = b2.memo
WHERE b1.id = '20250312162657-u184k20';
3 回帖
求助 SQL 使用两个 LEFT JOIN 时报错

欢迎来到这里！

我们正在构建一个小众社区，大家在这里相互信任，以平等 • 自由 • 奔放的价值观进行分享交流。最终，希望大家能够找到与自己志同道合的伙伴，共同成长。

注册 关于
请输入回帖内容 ...
  • EmberSky • • 1 赞同
    付费者

    把给表命名的 as 删掉试试

    SELECT
    Block1.content AS content1,
    Block2.content AS content2,
    Block3.content AS content3
FROM (
    SELECT
        memo,
        content
    FROM blocks
    WHERE id = '20250312162657-u184k20'
) Block1
LEFT JOIN (
    SELECT id, content, memo
    FROM blocks
) Block2 ON Block2.id = Block1.memo
LEFT JOIN (
    SELECT id, content
    FROM blocks
) Block3 ON Block3.id = Block2.memo;

    或者试下 ai 改后的

    SELECT
    Block1.content AS content1,
    Block2.content AS content2,
    Block3.content AS content3
FROM (
    SELECT
        memo,
        content
    FROM blocks
    WHERE id = '20250312162657-u184k20'
) AS Block1
LEFT JOIN (
    SELECT 
        id AS Block2_id,
        content AS Block2_content,
        memo AS Block2_memo
    FROM blocks
) AS Block2 ON Block1.memo = Block2.Block2_id
LEFT JOIN (
    SELECT 
        id AS Block3_id,
        content AS Block3_content
    FROM blocks
) AS Block3 ON Block2.Block2_memo = Block3.Block3_id;
  • 其他回帖
  • wilsons • • 1 赞同
    付费者 捐赠者

    思源中不可以，但使用其他客户端工具查询是可以的。

    可能是低版本的 sqlite 不支持链式 join，改进方式：用括号明确优先级

    这样可以

    SELECT
    Block1.content AS content1,
    Block2.content AS content2,
    Block3.content AS content3
FROM ((
    SELECT
        memo,
        content
    FROM blocks
    WHERE id = '20250312162657-u184k20'
) AS Block1
LEFT JOIN (
    SELECT id, content, memo
    FROM blocks
) AS Block2 ON Block2.id = Block1.memo)
LEFT JOIN (
    SELECT id, content
    FROM blocks
) AS Block3 ON Block3.id = Block2.memo;

    但建议简化成这样，性能更优

    SELECT
    b1.content AS content1,
    b2.content AS content2,
    b3.content AS content3
FROM (blocks b1
LEFT JOIN blocks b2 ON b2.id = b1.memo)
LEFT JOIN blocks b3 ON b3.id = b2.memo
WHERE b1.id = '20250312162657-u184k20';
    1 回复
    2 操作
    wilsons 在 2025-03-12 18:20:37 更新了该回帖
    wilsons 在 2025-03-12 18:13:33 更新了该回帖
  • Ximkool •
    订阅者 作者

    谢谢大佬 👍

Ximkool
深圳
回帖
4
 帖子
3
 积分
373