如何用单SELECT语句关联Table A与对应所有Table B行并创建视图

现有如下数据集：

Table A #

Table B #

由于业务限制，Table B的行必须先于Table A插入，因此用b_ids字段存储关联的Table B的id列表。我尝试用单条SELECT语句查询Table A的行及对应的所有Table B的值，并创建可过滤的视图，但执行SELECT * FROM A LEFT JOIN B ON B.id IN (A.b_ids);时，每个Table A行只返回首个匹配的Table B行，换INNER JOIN、RIGHT JOIN等类型结果都一样。

需要实现的输出形式如下（或类似结构）：

注：必须以Table A作为主表，实际场景中还要关联其他表。

你遇到的问题根源在于B.id IN (A.b_ids)的逻辑——数据库会把A.b_ids当成完整的字符串，而不是拆分后的id列表，所以只会匹配到第一个符合条件的id（比如2,3只会匹配id=2，因为2 IN ('2,3')在字符串匹配中成立，但3 IN ('2,3')不成立）。

要实现需求，需要先把b_ids字段的字符串拆分成单个id，再和Table B关联。以下是主流数据库的实现方式：

MySQL（8.0及以上版本） #

使用JSON_TABLE函数拆分字符串：

SELECT 
    A.id AS a_id,
    A.value AS a_value,
    A.b_ids,
    B.id AS b_id,
    B.value AS b_value
FROM A
LEFT JOIN JSON_TABLE(
    CONCAT('[', REPLACE(A.b_ids, ',', '","'), ']'),
    '$[*]' COLUMNS(b_id INT PATH '$')
) AS split_ids ON 1=1
LEFT JOIN B ON split_ids.b_id = B.id;

PostgreSQL #

使用string_to_array配合unnest拆分字符串：

SELECT 
    A.id AS a_id,
    A.value AS a_value,
    A.b_ids,
    B.id AS b_id,
    B.value AS b_value
FROM A
LEFT JOIN unnest(string_to_array(A.b_ids, ',')) AS split_ids(b_id) ON 1=1
LEFT JOIN B ON CAST(split_ids.b_id AS INT) = B.id;

SQL Server #

使用STRING_SPLIT函数拆分字符串：

SELECT 
    A.id AS a_id,
    A.value AS a_value,
    A.b_ids,
    B.id AS b_id,
    B.value AS b_value
FROM A
LEFT JOIN STRING_SPLIT(A.b_ids, ',') AS split_ids ON 1=1
LEFT JOIN B ON CAST(split_ids.value AS INT) = B.id;

可过滤视图创建示例（以MySQL为例） #

如果要创建支持过滤的视图，直接将上述查询封装即可：

CREATE VIEW A_B_JOIN_VIEW AS
SELECT 
    A.id AS a_id,
    A.value AS a_value,
    A.b_ids,
    B.id AS b_id,
    B.value AS b_value
FROM A
LEFT JOIN JSON_TABLE(
    CONCAT('[', REPLACE(A.b_ids, ',', '","'), ']'),
    '$[*]' COLUMNS(b_id INT PATH '$')
) AS split_ids ON 1=1
LEFT JOIN B ON split_ids.b_id = B.id;

这样查询视图时就能得到每个A行对应的所有关联B行，同时保留A作为主表的关联逻辑，方便后续关联其他表。

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