edis0n0
V2EX  ›  数据库

前端初学 SQL,求帮忙修改一个简单 MySQL 联查语句

  •  
  •   edis0n0 · Nov 4, 2022 · 2453 views
    This topic created in 1473 days ago, the information mentioned may be changed or developed.
    ```
    SELECT A.ItemId, Description, CreatedAt, B.GroupTitle FROM items A, groups B WHERE A.GroupId = B.GroupId AND UserId = 1 ORDER BY ItemId
    ```

    这个查询会导致如果 A 的 GroupId 为 NULL ,A 这条记录就会被忽略掉。我想要的效果是该记录 GroupTitle 变成 NULL 或者空字符串,剩下的字段都在,研究了很久都没成功。
    5 replies  •  2022-11-04 10:59:33 +08:00
    PendingOni
    PendingOni
    Nov 4, 2022❤️1
    1
    A 去 left join B?
    ```SQL
    SELECT A.ItemId, Description, CreatedAt, B.GroupTitle
    FROM items A
    left join
    groups B on A.GroupId = B.GroupId
    WHERE UserId = 1
    ORDER BY ItemId
    ```
    kkwa56188
    kkwa56188
    Nov 4, 2022❤️1
    2
    -- 也可以用 子查询. 这里用了个 max()是怕 B.GroupId 不唯一
    SELECT A.ItemId, A.Description, A.CreatedAt
    , ( select max(B.GroupTitle) from groups B WHERE B.GroupId = A.GroupId )
    FROM items A
    AND A.UserId = 1
    ORDER BY A.ItemId;
    akira
    akira
    Nov 4, 2022❤️1
    3
    `
    SELECT
    A.ItemId,
    A.Description,
    A.CreatedAt,
    B.GroupTitle
    FROM items A
    left join groups B on A.GroupId = B.GroupId
    WHERE 1=1
    AND UserId = 1
    ORDER BY ItemId
    `
    xuelu520
    xuelu520
    Nov 4, 2022❤️1
    4
    left join 就行。
    楼主可以去看看 left/right/inner 几种的区别,几种 join 用的还是很多的。
    edis0n0
    edis0n0
    OP
    Nov 4, 2022
    5
    成功了,谢谢大家
    About   ·   Help   ·   Advertise   ·   Blog   ·   API   ·   FAQ   ·   Privacy   ·   Solana   ·   2204 Online   Highest 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 33ms · UTC 14:35 · PVG 22:35 · LAX 06:35 · JFK 09:35
    ♥ Do have faith in what you're doing.