如何生成列动态适配关联表行数据的SQL视图 附果汁定价场景示例

实现方案 #

你要的动态生成杯型列的效果属于动态行转列需求，普通静态SQL无法实现，需要用动态SQL拼接查询语句，以下是MySQL环境的可运行代码：

SET @sql = NULL;
-- 第一步：从杯型表动态读取所有杯型，拼接对应的售价计算字段
SELECT GROUP_CONCAT(DISTINCT
    CONCAT('MAX(IF(st.serve_name = ''', serve_name, ''', st.price_offset * f.price, NULL)) AS `', serve_name, ' serve`')
) INTO @sql
FROM serve_types;

-- 第二步：拼接完整的查询语句
SET @sql = CONCAT('SELECT f.name AS name, ', @sql, ' 
FROM fruits f
LEFT JOIN serve_types st ON 1=1
WHERE f.milkshakeable = 0
GROUP BY f.id, f.name
ORDER BY f.name');

-- 第三步：执行拼接好的动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

说明 #

• 该方案无需手动指定杯型名称，后续表B新增/删除/修改杯型时，直接执行代码就会自动调整结果列，和你硬编码的查询结果完全一致
• 如果需要统一控制小数位数，可以把st.price_offset * f.price替换为ROUND(st.price_offset * f.price, 4)来固定保留4位小数
• 其他数据库（PostgreSQL、SQL Server等）思路完全一致，仅动态SQL语法、行转列函数的写法略有不同

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