MySQL关联查询结果行转列及生成指定XML结构求助

一、将多张图片转为同一行多列 #

1. 静态列（已知图片数量上限） #

如果能确定单台机器的图片数量有固定上限，用条件聚合即可实现行转列，示例（假设最多3张图片）：

SELECT
  m.ref_no,
  m.make,
  m.model,
  MAX(CASE WHEN rn = 1 THEN i.image_name END) AS image_1,
  MAX(CASE WHEN rn = 2 THEN i.image_name END) AS image_2,
  MAX(CASE WHEN rn = 3 THEN i.image_name END) AS image_3
FROM (
  SELECT 
    image_name, 
    machine_id,
    ROW_NUMBER() OVER(PARTITION BY machine_id ORDER BY image_name) AS rn
  FROM images
) i
INNER JOIN machinery m ON i.machine_id = m.id
WHERE m.ref_no = 1234
GROUP BY m.ref_no, m.make, m.model;

通过ROW_NUMBER()给单台机器的图片编号，再用CASE配合聚合函数把不同编号的图片映射到独立列。

2. 动态列（图片数量不固定） #

如果图片数量无固定上限，用动态SQL自动生成对应列（以MySQL为例）：

-- 生成动态列名
SET @cols = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN rn = ', rn, ' THEN image_name END) AS image_', rn))
INTO @cols
FROM (
  SELECT ROW_NUMBER() OVER(PARTITION BY machine_id ORDER BY image_name) AS rn
  FROM images
  WHERE machine_id = (SELECT id FROM machinery WHERE ref_no = 1234)
) t;

-- 拼接并执行SQL
SET @sql = CONCAT('
SELECT m.ref_no, m.make, m.model, ', @cols, '
FROM (
  SELECT 
    image_name, 
    machine_id,
    ROW_NUMBER() OVER(PARTITION BY machine_id ORDER BY image_name) AS rn
  FROM images
) i
INNER JOIN machinery m ON i.machine_id = m.id
WHERE m.ref_no = 1234
GROUP BY m.ref_no, m.make, m.model;
');

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

该方案会根据目标机器的实际图片数量，自动生成对应数量的图片列。

二、生成包含<Files>节点的XML结构 #

1. 数据库端直接生成XML #

用字符串拼接直接输出目标XML结构（以MySQL为例）：

SELECT CONCAT(
  '<Machine>',
  '<RefNo>', m.ref_no, '</RefNo>',
  '<Make>', m.make, '</Make>',
  '<Model>', m.model, '</Model>',
  '<Files>',
    GROUP_CONCAT('<File>', i.image_name, '</File>' SEPARATOR ''),
  '</Files>',
  '</Machine>'
) AS machine_xml
FROM images i
INNER JOIN machinery m ON i.machine_id = m.id
WHERE m.ref_no = 1234
GROUP BY m.ref_no, m.make, m.model;

查询结果直接返回符合要求的XML，每张图片对应一个<File>子节点，嵌套在<Files>下。

2. Wappler端处理生成XML #

如果更倾向于在Wappler中控制输出：

• 先用原查询获取全量数据（重复机器信息+对应图片）
• 在Server Connect中添加Group By步骤，按ref_no、make、model分组，将每组的image_name收集为数组
• 使用Wappler的XML模板渲染，示例模板：

<Machine>
  <RefNo>{{ref_no}}</RefNo>
  <Make>{{make}}</Make>
  <Model>{{model}}</Model>
  <Files>
    {{each image in images}}
    <File>{{image.image_name}}</File>
    {{/each}}
  </Files>
</Machine>

关于Pivot Tables的说明 #

Pivot表本质就是聚合函数+条件判断的行转列实现，和上述静态列方案原理一致，完全适用。以SQL Server为例，用Pivot语法的写法更简洁：

SELECT ref_no, make, model, image_1, image_2, image_3
FROM (
  SELECT 
    m.ref_no,
    m.make,
    m.model,
    i.image_name,
    'image_' + CAST(ROW_NUMBER() OVER(PARTITION BY m.id ORDER BY i.image_name) AS VARCHAR(10)) AS image_col
  FROM images i
  INNER JOIN machinery m ON i.machine_id = m.id
  WHERE m.ref_no = 1234
) t
PIVOT (
  MAX(image_name)
  FOR image_col IN (image_1, image_2, image_3)
) p;

这种写法适合图片数量固定的场景，提前指定列名即可。

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