Firebird 2.5-与SQL中的STUFF等效的功能（在MySql中为GROUP_CONCAT /在Oracle中为LISTAGG） [英] Firebird 2.5 - Equivalent function to STUFF in SQL (GROUP_CONCAT in MySql / LISTAGG In Oracle)

问题描述

有人知道Firebird 2.5是否具有类似于SQL中的 STUFF功能的功能？
我有一个表，其中包含父用户记录，而另一个表中包含与父用户相关的子用户记录。我希望能够拉出用户用逗号分隔的 ROLES字符串，而不必使用第二个查询，循环返回给定ID的值并自己创建字符串。

Does anyone know if Firebird 2.5 has a function similar to the "STUFF" function in SQL? I have a table which contains parent user records, and another table which contains child user records related to the parent. I'd like to be able to pull a comma delimited string of the "ROLES" the user has without having to use a second query, loop over the values returned for the given ID and create the string myself.

我已经搜索了其他任何相关问题，但没有找到任何问题。
此链接中的问题要连接的Sum等效字符串为基本上我也想做，但是有了Firebird 2.5数据库。

I've searched for any other related questions, but have not found any. The question in this link string equivalent of Sum to concatenate is basically what I want to do too, but with the Firebird 2.5 database.

推荐答案

看起来您很幸运-Firebird 2.1具有 LIST（）聚合函数，其作用类似于MySql中的 GROUP_CONCAT ，它允许这样的查询：

It looks like you are in luck - Firebird 2.1 has a LIST() aggregate function which works like GROUP_CONCAT in MySql, which allows a query like so:

SELECT p.Name, LIST(c.Name, ', ')
FROM parent p INNER JOIN child c on c.parentid = p.parentid
GROUP by p.Name;

编辑，重新订购

在应用 LIST 聚合函数之前，可以通过对派生表中的数据进行预排序来影响排序，如下所示：

You may be able to influence ordering by pre-ordering the data in a derived table, prior to applying the LIST aggregation function, like so:

SELECT x.ParentName, LIST(x.ChildName, ', ')
FROM 
(
  SELECT p.Name as ParentName, c.Name as ChildName
  FROM parent p INNER JOIN child c on c.parentid = p.parentid
  ORDER BY c.Name DESC
) x
GROUP by x.ParentName;

这篇关于Firebird 2.5-与SQL中的STUFF等效的功能（在MySql中为GROUP_CONCAT /在Oracle中为LISTAGG）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！