Oracle组示例 [英] Oracle group by example

问题描述

大家好，我有一个简单的查询，显示来自用户的信息，可以为用户分配多个办公室，现在我想将这些用户分组到办公室，如下所示:

Hi guys i have a simple query that displays information from users, users can have more than 1 office assigned, now i want to group those users in offices look below:

select u.name, r.region, r.office
from user u
join region r on u.regionID=r.regionID

Results
row  name   region  office
1    SCOTT  FLORIDA MIAMI
2    SCOTT  FLORIDA TAMPA
3    SANDRA TEXAS   DALLAS
4    SOPHIA TEXAS   KILLEEN
5    SOPHIA TEXAS   DALLAS

Wanted Results
row  name   region  office
1    SCOTT  FLORIDA MIAMI
                    TAMPA
2    SANDRA TEXAS   DALLAS
3    SOPHIA TEXAS   KILLEEN
                    DALLAS

LISTAGG函数在10g上不起作用，正在寻找替代方法...

The LISTAGG function doesn''t work on 10g, searching for an alternative...

推荐答案

我对Oracle没有任何经验，因此从搜索工作中就可以使用，并且未经测试.

我找到了 LISTAGG [

I have no experience with Oracle so this from search work and is not tested.

I found the LISTAGG[^] statement, which should be available from Oracle 10g.

You statement would look like

SELECT u.name, r.region, LISTAGG(r.office, ' ') WITHIN GROUP (ORDER BY NULL)
FROM user u
JOIN region r on u.regionID = r.regionID
GROUP BY u.name, r.region
ORDER BY 1, 2 NULLS LAST

我从 Oracle LISTAGG函数 [

I have taken this from Oracle LISTAGG function [^].

找到了这个http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php [ ^ ]
这有帮助

Found this http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php[^]
This helps

这篇关于Oracle组示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！