如何通过单SQL查询实现数据表与字段定义表的字段匹配转换？

首先明确示例表结构（方便理解方案）：

-- FIELD_DEF表：存储字段ID与标题
CREATE TABLE FIELD_DEF (
    FIELDID INT,
    FIELD_CAPTION VARCHAR(50)
);
INSERT INTO FIELD_DEF VALUES (1, '姓名'), (2, '年龄'), (3, '邮箱');

-- DATA_TABLE表：存储数据ID及各字段实际值
CREATE TABLE DATA_TABLE (
    DATAID INT,
    FIELD1 VARCHAR(50),
    FIELD2 INT,
    FIELD3 VARCHAR(50)
);
INSERT INTO DATA_TABLE VALUES (101, '张三', 28, 'zhangsan@example.com');

场景一：字段数量固定且已知 #

如果DATA_TABLE的字段数量固定，可直接通过多表关联+字符串拼接实现：

方案1：分字段展示标题与数据 #

SELECT
    d.DATAID,
    CONCAT(f1.FIELD_CAPTION, ': ', d.FIELD1) AS 姓名信息,
    CONCAT(f2.FIELD_CAPTION, ': ', d.FIELD2) AS 年龄信息,
    CONCAT(f3.FIELD_CAPTION, ': ', d.FIELD3) AS 邮箱信息
FROM DATA_TABLE d
LEFT JOIN FIELD_DEF f1 ON f1.FIELDID = 1
LEFT JOIN FIELD_DEF f2 ON f2.FIELDID = 2
LEFT JOIN FIELD_DEF f3 ON f3.FIELDID = 3;

方案2：合并所有标题与数据到同一列 #

SELECT
    d.DATAID,
    CONCAT_WS(' | ',
        CONCAT(f1.FIELD_CAPTION, ': ', d.FIELD1),
        CONCAT(f2.FIELD_CAPTION, ': ', d.FIELD2),
        CONCAT(f3.FIELD_CAPTION, ': ', d.FIELD3)
    ) AS 完整数据
FROM DATA_TABLE d
LEFT JOIN FIELD_DEF f1 ON f1.FIELDID = 1
LEFT JOIN FIELD_DEF f2 ON f2.FIELDID = 2
LEFT JOIN FIELD_DEF f3 ON f3.FIELDID = 3;

场景二：字段数量动态变化（适配新增字段） #

如果字段可能新增，无需修改SQL即可适配，可使用动态SQL，不同数据库语法如下：

MySQL 实现 #

SET @sql = NULL;
-- 生成字段拼接语句
SELECT GROUP_CONCAT(
    CONCAT('CONCAT(f', FIELDID, '.FIELD_CAPTION, ": ", d.FIELD', FIELDID, ') AS `', FIELD_CAPTION, '`')
) INTO @sql FROM FIELD_DEF;

-- 拼接完整查询语句
SET @sql = CONCAT(
    'SELECT d.DATAID, ', @sql, ' FROM DATA_TABLE d ',
    (SELECT GROUP_CONCAT(' LEFT JOIN FIELD_DEF f', FIELDID, ' ON f', FIELDID, '.FIELDID = ', FIELDID) FROM FIELD_DEF)
);

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Server 实现 #

DECLARE @sql NVARCHAR(MAX);
-- 生成字段拼接语句
SELECT @sql = STRING_AGG(
    CONCAT('CONCAT(f', FIELDID, '.FIELD_CAPTION, '': '', d.FIELD', FIELDID, ') AS [', FIELD_CAPTION, ']'),
    ', '
) FROM FIELD_DEF;

-- 拼接完整查询语句
SELECT @sql = CONCAT(
    'SELECT d.DATAID, ', @sql, ' FROM DATA_TABLE d ',
    (SELECT STRING_AGG(' LEFT JOIN FIELD_DEF f' + CAST(FIELDID AS VARCHAR) + ' ON f' + CAST(FIELDID AS VARCHAR) + '.FIELDID = ' + CAST(FIELDID AS VARCHAR), ' ') FROM FIELD_DEF)
);

-- 执行动态SQL
EXEC sp_executesql @sql;

动态SQL会自动读取FIELD_DEF中的字段定义，生成对应查询逻辑，新增字段时无需修改SQL语句。

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