SQLAnywhere 16 UltraLite如何替代FOR XML实现多行聚合拼接

• 你遇到的语法报错核心原因：UltraLite 16.0 是轻量嵌入式数据库，本身不支持FOR XML系列语法，不管是FOR XML PATH还是FOR XML AUTO都没有在该版本中提供，因此无法直接套用SQL Server上的FOR XML拼接方案。

推荐方案：使用内置LIST()聚合函数 #

UltraLite 16.0 内置了专门用于字符串拼接的LIST()聚合函数，写法比FOR XML方案更简洁，性能也更好，对应查询语句如下：

SELECT
  f.nmFilm AS mFilm,
  LIST(g.idGenre, ', ') AS idGenre
FROM film f
LEFT JOIN film_genre fg ON f.idFilm = fg.idFilm
LEFT JOIN genre g ON fg.idGenre = g.idGenre
GROUP BY f.idFilm, f.nmFilm;

函数使用说明：

• 第一个参数传入需要拼接的字段，第二个参数为自定义分隔符，省略时默认使用逗号作为分隔符
• 函数会自动跳过分组内的NULL值，不会生成多余的前置/后置分隔符
• 如果需要指定拼接值的排序规则，可以在函数内添加ORDER BY子句，例如要按genre ID升序拼接，可写为LIST(g.idGenre ORDER BY g.idGenre, ', ')

兼容方案：递归CTE实现（适用于未打补丁的早期16.0版本） #

如果你使用的是未更新补丁的极早期UltraLite 16.0版本，无法使用LIST()函数，可以通过递归CTE逐行拼接实现相同效果：

WITH genre_sorted AS (
  SELECT
    f.idFilm,
    f.nmFilm,
    g.idGenre,
    ROW_NUMBER() OVER (PARTITION BY f.idFilm ORDER BY g.idGenre) AS row_num,
    COUNT(*) OVER (PARTITION BY f.idFilm) AS genre_count
  FROM film f
  LEFT JOIN film_genre fg ON f.idFilm = fg.idFilm
  LEFT JOIN genre g ON fg.idGenre = g.idGenre
),
genre_concat AS (
  -- 锚点：取每个电影关联的第一个分类
  SELECT
    idFilm,
    nmFilm,
    CAST(idGenre AS VARCHAR(1000)) AS genre_list,
    row_num,
    genre_count
  FROM genre_sorted
  WHERE row_num = 1
  UNION ALL
  -- 递归：逐行追加后续分类
  SELECT
    s.idFilm,
    s.nmFilm,
    c.genre_list || ', ' || s.idGenre,
    s.row_num,
    s.genre_count
  FROM genre_concat c
  INNER JOIN genre_sorted s
    ON c.idFilm = s.idFilm
    AND s.row_num = c.row_num + 1
)
-- 取每个电影拼接完成的最终结果
SELECT
  nmFilm AS mFilm,
  genre_list AS idGenre
FROM genre_concat
WHERE row_num = genre_count;

优先选择LIST()函数方案，代码易维护且执行效率远高于递归写法，UltraLite 16.0正式发布版已完整支持该函数。

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