MySQL如何将关联图片表的行转为front_image与back_image列

核心思路是使用条件聚合结合JSON字段提取函数，将同一条listing下的正反面图片数据合并到同一行的不同列中。

以下是MySQL环境下的可用查询语句，支持5.7及以上版本：

SELECT 
    l.id,
    l.title,
    MAX(CASE WHEN i.properties->>'$.type' = 'front' THEN i.file_name END) AS front_image,
    MAX(CASE WHEN i.properties->>'$.type' = 'back' THEN i.file_name END) AS back_image
FROM listings l
LEFT JOIN images i ON i.listings_id = l.id
GROUP BY l.id, l.title
ORDER BY l.id;

语句说明 #

• i.properties->>'$.type'是MySQL的JSON语法糖，等价于JSON_UNQUOTE(JSON_EXTRACT(i.properties, '$.type'))，作用是提取properties字段中type属性的纯文本值
• CASE WHEN语句会对每行图片做类型判断，匹配对应类型就返回文件名，不匹配返回NULL
• MAX聚合函数会忽略NULL值，将同一条listing下的两个图片文件名分别合并到对应列，最终实现一行返回一条listing的完整信息
• 如果你使用的是PostgreSQL等其他数据库，只需要将JSON提取的语法替换为对应数据库的写法即可，其余逻辑通用

内容的提问来源于stack exchange，提问作者Carol.Kar