如何将第二张表的匹配记录关联至第一张表的行

基础表（base table） #

设计表（design table） #

我尝试的SQL语句 #

select * from base b inner join design d on b.bid=d.bid

期望的JSON响应 #

{
"bid" :  1,
"b_name" : "john",
"b_lname" : "nathan",
"description" : "aa",
"designpoints" :
[
  {
 "id" :  1, 
 "designname" :  "test1",
 "designdescription" : "test description",
 "speed" :  20,
 "bid" :  1

  },
 {
 "id" :  2, 
 "designname" :  "test2",
 "designdescription" : "test description2",
 "speed" :  21,
 "bid" :  1

  }
]
}

期望的输出 #

解决方案 #

场景1：生成嵌套JSON格式 #

根据数据库类型，使用JSON聚合函数将匹配的设计表记录转为数组嵌套在基础表行中：

MySQL（5.7+）版本

SELECT 
    b.bid,
    b.b_name,
    b.b_lname,
    b.description,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'id', d.id,
            'designname', d.designname,
            'designdescription', d.designdescription,
            'speed', d.speed,
            'bid', d.bid
        )
    ) AS designpoints
FROM base b
LEFT JOIN design d ON b.bid = d.bid
GROUP BY b.bid, b.b_name, b.b_lname, b.description;

如果只需要保留有匹配记录的行，将LEFT JOIN替换为INNER JOIN即可。

PostgreSQL（9.3+）版本

SELECT 
    b.bid,
    b.b_name,
    b.b_lname,
    b.description,
    json_agg(
        json_build_object(
            'id', d.id,
            'designname', d.designname,
            'designdescription', d.designdescription,
            'speed', d.speed,
            'bid', d.bid
        )
    ) AS designpoints
FROM base b
LEFT JOIN design d ON b.bid = d.bid
GROUP BY b.bid, b.b_name, b.b_lname, b.description;

场景2：生成拼接字符串格式 #

将匹配的设计表记录按指定格式拼接成字符串，添加到基础表的新列中：

MySQL版本

SELECT 
    b.bid,
    b.b_name,
    b.b_lname,
    b.description,
    GROUP_CONCAT(
        CONCAT('#', d.id, '#', d.designname, '#', d.designdescription, '#', d.speed, '#', d.bid)
        SEPARATOR '' -- 若多条记录需要分隔，可改为'|'等符号
    ) AS ConcatOutput
FROM base b
LEFT JOIN design d ON b.bid = d.bid
GROUP BY b.bid, b.b_name, b.b_lname, b.description;

注：若bid=1有两条记录，默认会拼接成#1#test1#test description#20#1#2#test2#test description2#21#1，如果只需要单条记录，可用MAX()替代GROUP_CONCAT()。

PostgreSQL版本

SELECT 
    b.bid,
    b.b_name,
    b.b_lname,
    b.description,
    STRING_AGG(
        CONCAT('#', d.id, '#', d.designname, '#', d.designdescription, '#', d.speed, '#', d.bid),
        '' -- 分隔符按需调整
    ) AS ConcatOutput
FROM base b
LEFT JOIN design d ON b.bid = d.bid
GROUP BY b.bid, b.b_name, b.b_lname, b.description;

内容的提问来源于stack exchange，提问作者Manish S