Informix 中的 group_concat
在 Informix 的 SQL 中查找将模拟 MySQL 的 group_concat 函数的查询。
MySQL 的group_concat 的作用是创建组中所有成员的枚举。
因此，数据如下：
| orderid | itemName | Price |
|---|---|---|
| 1 | Paper | 10 |
| 1 | Pen | 5 |
| 2 | Sugar | 15 |
和以下查询：
select group_concat(itemName), sum(price)
from order_details
group by orderid
将产生：
| items | Price |
|---|---|
| Paper,Pen | 15 |
| Sugar | 15 |
在 Informix 中实现此目的最有效的方法是什么？ 我们一定要使用存储过程吗？
Looking for a query in Informix's SQL that will simulate MySQL's group_concat function.
What MySQL's group_concat does is it creates an enumeration of all members in the group.
So with the data as follows:
| orderid | itemName | price |
|---|---|---|
| 1 | Paper | 10 |
| 1 | Pen | 5 |
| 2 | Sugar | 15 |
and the following query:
select group_concat(itemName), sum(price)
from order_details
group by orderid
would produce:
| items | price |
|---|---|
| Paper,Pen | 15 |
| Sugar | 15 |
What would be most efficient way to achieve this in Informix? Would we definitely have to use a stored procedure?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您必须定义一个用户定义的聚合才能执行此操作。 它有四个部分 - 四个函数（搜索 在 IDS 12.10 信息中心):
这是大写的官方术语，比较直观。 考虑计算平均值。
组合器用于组合并行执行的中间结果； 每个并行执行都从迭代器开始并生成中间结果。 当并行执行完成时，单独的值集将通过组合器进行组合。
您可以在 IDS 中编写类似的代码 - 使用存储过程或 C 或 Java UDR。
请参阅SO问题对于 Informix 中实现的基于字符串的 GROUP_CONCAT() 函数，将一对多关系显示为 2 列 — 1 个唯一行（ID 和逗号分隔列表）。
You would have to define a user-defined aggregate to do this. That has four parts - four functions (search for CREATE AGGREGATE in the IDS 12.10 Info Centre):
That's the official terminology in capitals, and it is moderately intuitive. Think of calculating an average.
The combiner is used to combine intermediate results from parallel execution; each parallel execution starts with the iterator and generates intermediate results. When the parallel execution completes, the separate sets of values are combined with the combiner.
You can write analogous code in IDS - using stored procedures or C or Java UDRs.
See the SO question Show a one to many relationship as 2 columns — 1 unique row (ID & comma separated list) for a string-based GROUP_CONCAT() function implemented in Informix.
Informix 中当然没有内置函数可以执行此操作。 其他主流 RDBMS 是否有如此奇怪的聚合函数？ 按查询中未选择的列进行排序有点可疑，但是分组呢？ 这对我来说是一种新的体验。
您必须编写存储过程或 UDR 才能生成此类数据集。 老实说，我不会在数据库中尝试这样做。 它看起来像是最适合此输出的使用者（即应用程序/web应用程序/报告编写器等）的任务。
There is certainly no built-in function in Informix to do this. Does any other mainstream RDBMS have such a bizarre aggregate function? Ordering by a column not selected in a query is a bit dubious, but grouping? That's a new one on me.
You would have to write a stored procedure or UDR to generate such a dataset. To be honest, I wouldn't attempt this in the database. It looks like a task best suited to the consumer of this output (ie the application/webapp/report-writer etc).