从表1中选择所有列，从表2中选择一个列，按组分组? [英] Select all columns from table 1 and one column from table two which is grouped by?

问题描述

我有table1

id1 name1 address1
1    abc  add1
2    abc  add2

和表2

id2 name2 mother_name
1    abc   iii
2    abc   jjj

我想做

select table1.id1,table1.name1,XMLAGG(XMLELEMENT(E,table1.address1||',')).EXTRACT('//text()), table2.name,
       XMLAGG(XMLELEMENT(E,table2.mother_name||',')).EXTRACT('//text())
from table1 inner join table2 on table1.name1=table2.name2
group by table2.name2;

所以基本上输出将类似于

So basically output will be like

id1 name1 addr1      name2 xmlagg
1    abc  add1,add2   abc  iii,jjj

还

如果我也想对t1列之一进行分组，又该怎么办?

What if I want to do a group by for one of the t1 columns too?

推荐答案

我会在子查询中使用listagg():

select t1.*, xmlagg
from table1 t1 join
     (select name2, listagg(mother_name, ',') within group (order by mother_name) as xmlagg
      from table2 t2
      group by name2
     ) t2
     on t1.name1 = t2.name2;

上面的查询在加入之前进行汇总，因此可以使用t1.*.您也可以在加入后执行此操作:

The above query does the aggregation before the join, so it can use t1.*. You can also do it after the join:

select t1.name, listagg(mother_name, ',') within group (order by mother_name)
from table1 t1 join
     table2 t2
     on t1.name1 = t2.name2
group by t1.name;

这种形式使得向select中添加其他列变得更加困难，但是您可以按自己喜欢的方式进行汇总.

This form makes it harder to add additional columns to the select, but you can aggregate by anything you like.

这篇关于从表1中选择所有列，从表2中选择一个列，按组分组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！