MySQL 8.0下如何关联异构表实现指定列对比查询？

现有两张结构不同的表table_A（行存储属性）和table_B（列存储属性），需通过id关联对比对应数值。

表结构 #

table_A（行式结构） #

id   item_name   item_value
1    cost         1.00
1    price        3.15
1    tax          0.15
2    cost         9.00
2    price        17.66
2    tax          1.00

table_B（列式结构） #

id   cost   price   tax
1    1.20   3.80    0.20
2    8.95   15.65   0.98

期望结果 #

id   item_name   item_value_a   item_value_b
1    cost         1.00          1.20
1    price        3.15          3.80
1    tax          0.15          0.20
2    cost         9.00          8.95
2    price        17.66         15.65
2    tax          1.00          0.98

我已经尝试用UNION ALL将table_B转成行式结构，再和table_A关联，语句如下：

select id,'cost' as item_name,cost as item_value
from table_B 
union all
select id,'price' as item_name,price as item_value
from table_B
union all
select id,'tax' as item_name,tax as item_value
from table_B

但疑惑这种方法是否最优？当table_B有大量列时，难道要写大量UNION ALL？使用的是MySQL 8.0，求更优方案。

方法1：原生UNION ALL（适合列数量固定且较少的场景） #

你的初始方法逻辑清晰、性能稳定，在列数不多时是可靠选择。最终关联查询可写成：

SELECT 
    a.id,
    a.item_name,
    a.item_value AS item_value_a,
    b.item_value AS item_value_b
FROM table_A a
JOIN (
    SELECT id, 'cost' AS item_name, cost AS item_value FROM table_B
    UNION ALL
    SELECT id, 'price' AS item_name, price AS item_value FROM table_B
    UNION ALL
    SELECT id, 'tax' AS item_name, tax AS item_value FROM table_B
) b ON a.id = b.id AND a.item_name = b.item_name
ORDER BY a.id, a.item_name;

优点是直观易维护，缺点是列数较多时代码会冗长。

方法2：利用MySQL 8.0的JSON函数实现动态列转行（适合大量列的场景） #

MySQL 8.0支持JSON操作，可通过JSON_TABLE和JSON_OBJECT简化列转行逻辑，避免重复写UNION ALL：

SELECT 
    a.id,
    a.item_name,
    a.item_value AS item_value_a,
    b.item_value AS item_value_b
FROM table_A a
JOIN (
    SELECT 
        tb.id,
        jt.item_name,
        jt.item_value
    FROM table_B tb
    JOIN JSON_TABLE(
        JSON_OBJECT('cost', tb.cost, 'price', tb.price, 'tax', tb.tax),
        '$.*' COLUMNS (
            item_name VARCHAR(20) PATH '$[0]',
            item_value DECIMAL(10,2) PATH '$[1]'
        )
    ) jt
) b ON a.id = b.id AND a.item_name = b.item_name
ORDER BY a.id, a.item_name;

如需新增列，仅需在JSON_OBJECT中添加对应键值对即可，代码更简洁。

进阶：完全动态适配列（无需手动指定列名） #

如果table_B的列可能动态变化，可结合information_schema自动读取列名，生成动态转换逻辑：

SET @cols = (
    SELECT GROUP_CONCAT(
        CONCAT('''', COLUMN_NAME, ''', ', COLUMN_NAME)
    )
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() 
      AND TABLE_NAME = 'table_B' 
      AND COLUMN_NAME != 'id'
);

SET @sql = CONCAT('
SELECT 
    a.id,
    a.item_name,
    a.item_value AS item_value_a,
    b.item_value AS item_value_b
FROM table_A a
JOIN (
    SELECT 
        tb.id,
        jt.item_name,
        jt.item_value
    FROM table_B tb
    JOIN JSON_TABLE(
        JSON_OBJECT(', @cols, '),
        ''$.*'' COLUMNS (
            item_name VARCHAR(20) PATH ''$[0]'',
            item_value DECIMAL(10,2) PATH ''$[1]''
        )
    ) jt
) b ON a.id = b.id AND a.item_name = b.item_name
ORDER BY a.id, a.item_name;
');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

该方法会自动适配table_B中除id外的所有列，无需手动修改代码，适合列数多且可能变动的场景。

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