能否以t1行作为关联键查询两表并将t2字段映射至t1？

没问题，我来帮你搞定这个需求！根据你给出的表结构和示例数据，我们可以通过两种常见的方式来实现t2参数到t1的映射，分别对应横向展开同一行和纵向拆分多行的场景，你可以根据实际需求选择。

方式一：横向展开，同一行展示所有id关联信息 #

这种方式会把t1中每个id字段（id1-id4）对应的t2的info和param都放在同一行，适合需要完整查看单条t1记录所有关联信息的场景。

查询语句 #

SELECT
    t1.name,
    t1.year,
    -- 映射id1对应的t2信息
    t2_1.info AS id1_info,
    t2_1.param AS id1_param,
    -- 映射id2对应的t2信息
    t2_2.info AS id2_info,
    t2_2.param AS id2_param,
    -- 映射id3对应的t2信息
    t2_3.info AS id3_info,
    t2_3.param AS id3_param,
    -- 映射id4对应的t2信息
    t2_4.info AS id4_info,
    t2_4.param AS id4_param
FROM t1
LEFT JOIN t2 AS t2_1 ON t1.id1 = t2_1.id
LEFT JOIN t2 AS t2_2 ON t1.id2 = t2_2.id
LEFT JOIN t2 AS t2_3 ON t1.id3 = t2_3.id
LEFT JOIN t2 AS t2_4 ON t1.id4 = t2_4.id;

查询结果 #

根据你给出的示例数据，执行后会得到如下结果：

扩展说明 #

如果你的t1表有更多类似idX的字段（比如id5、id6...），只需要按照相同的模式继续添加LEFT JOIN t2 AS t2_X ON t1.idX = t2_X.id，并在SELECT子句中添加对应的t2_X.info AS idX_info和t2_X.param AS idX_param即可，扩展性很强。

方式二：纵向拆分，每个非null id对应一行 #

如果需要把t1中每个非null的id字段单独拆成一行展示，方便逐个查看每个关联信息，可以用UNION ALL来实现。

查询语句 #

SELECT
    t1.name,
    t1.year,
    'id1' AS id_column,
    t2.info,
    t2.param
FROM t1
LEFT JOIN t2 ON t1.id1 = t2.id
WHERE t1.id1 IS NOT NULL

UNION ALL

SELECT
    t1.name,
    t1.year,
    'id2' AS id_column,
    t2.info,
    t2.param
FROM t1
LEFT JOIN t2 ON t1.id2 = t2.id
WHERE t1.id2 IS NOT NULL

UNION ALL

SELECT
    t1.name,
    t1.year,
    'id3' AS id_column,
    t2.info,
    t2.param
FROM t1
LEFT JOIN t2 ON t1.id3 = t2.id
WHERE t1.id3 IS NOT NULL

UNION ALL

SELECT
    t1.name,
    t1.year,
    'id4' AS id_column,
    t2.info,
    t2.param
FROM t1
LEFT JOIN t2 ON t1.id4 = t2.id
WHERE t1.id4 IS NOT NULL
ORDER BY t1.name, t1.year, id_column;

查询结果 #

执行后会得到如下结果：

说明 #

这里用WHERE t1.idX IS NOT NULL过滤掉了id为null的记录，如果需要保留null的id行（对应info和param为null），去掉WHERE条件即可。

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