向现有查询添加分组行的逗号分隔值 [英] Add comma-separated value of grouped rows to existing query

问题描述

我有一个查看报告的视图，看起来像这样：

I've got a view for reports, that looks something like this:

SELECT
  a.id,
  a.value1,
  a.value2,
  b.value1,
  /* (+50 more such columns)*/
FROM a
JOIN b ON (b.id = a.b_id)
JOIN c ON (c.id = b.c_id)
LEFT JOIN d ON (d.id = b.d_id)
LEFT JOIN e ON (e.id = d.e_id)
/* (+10 more inner/left joins) */

它加入了很多表，返回了很多列，但是索引已经就位，性能很好。

It joins quite a few tables and returns lots of columns, but indexes are in place and performance is fine.

现在我想添加结果的另一列，显示

Now I want to add another column to the result, showing

• 逗号分隔值


• >
  
• 从表y


• 外层通过交叉表x加入


• 如果 a.value3 IS NULL ，否则取 a.value3

• comma-separated values
• ordered by value
• from table y
• outer joined via intersection table x
• if a.value3 IS NULL, else take a.value3

要逗号分隔分组的值，请使用 Tom Kyte的stragg ，可以使用

To comma-separate the grouped values I use Tom Kyte's stragg, could use COLLECT later.

SELECT的伪代码将会显示在下面的代码中：

Pseudo-code for the SELECT would look like that:

SELECT xx.id, COALESCE( a.value3, stragg( xx.val ) ) value3
FROM (
  SELECT x.id, y.val
  FROM x
  WHERE x.a_id = a.id
  JOIN y ON ( y.id = x.y_id )
  ORDER BY y.val ASC
) xx
GROUP BY xx.id

什么是最好的方法呢？任何提示？

What is the best way to do it? Any tips?

推荐答案

Oracle 11.2.x有一个新功能LISTAGG。也许这个新功能到达太晚了吗？

Oracle 11.2.x has a new function LISTAGG. Maybe this new function is arriving too late for you?

示例（来自Ora Mag的TK）：

example (From TK in Ora Mag):

SQL> select deptno,
  2         listagg( ename, '; ' )
  3         within group
  4         (order by ename) enames
  5     from emp
  6    group by deptno
  7    order by deptno;

  DEPTNO   ENAMES
---------  --------------------
  10       CLARK; KING; MILLER
  20       ADAMS; FORD; JONES;
           SCOTT; SMITH

  30       ALLEN; BLAKE;
           JAMES; MARTIN;
           TURNER; WARD

这篇关于向现有查询添加分组行的逗号分隔值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！