Oracle字符串聚合 [英] Oracle string aggregation
问题描述
我的表结构如下所示,我是该领域的新手.我知道基本的查询,但是对我来说很复杂.请帮助我做到这一点.
My table structure look like this, I am new for the this field. I know the basic queries.But it's complicated for me. Please help me to do this.
表结构
Customer Product piriority
10001 Main_product 1
10001 Sub_product1 2
10001 Sub_product2 2
10001 Sub_product3 2
10001 Sub_product4 2
10002 Main_product 1
10002 Sub_product1 2
10002 Sub_product2 2
预期输出:
Customer Main_Product Sub_product
10001 Main_product Sub_product1,Sub_product2,Sub_product3,Sub_product4
10002 Main_product Sub_product1,Sub_product2
推荐答案
我将假设PRIORITY
列在存在主要产品"时始终为1,而在其他任何时候均不会为1.从您的数据看来,每个客户也只有一个主要"产品.我将假设这是真的.如果不是,那么您应该有另一列来区分产品组.您可以简单地将其添加到下面.
I'm going to assume that the PRIORITY
column is always 1 when there's a "main product" and never 1 any other time. From your data it also looks like each customer has only one "main" product. I'm going to assume that this is true. If it's not then you should have another column to distinguish product groups. You can simply add this into the below.
复杂/有效的答案可能如下:
The complicated/efficient answer may be as follows:
select customer
, max(product) keep (dense_rank first order by priority) as main_product
, listagg(case when priority = 2 then product end, ', ')
within group (order by product) as sub_product
from products
group by customer
每个客户的PRODUCT
列假定每个客户都有一个主要产品,然后按优先级顺序获取第一个产品.第二列仅在优先级为2的情况下使用,并使用字符串连接功能
Per customer, the PRODUCT
column assumes that every customer has a main product, then gets the first product in order by priority. The second column only takes where the priority is 2 and uses the string concatenation function LISTAGG() to concatenate your values together.
我强烈推荐Rob van Wijk的关于KEEP子句的博客文章.
I would highly recommend Rob van Wijk's blog post about the KEEP clause.
更标准的SQL解决方案如下所示:
A more standard SQL solution would look like this:
select a.customer, a.product as main_product
, listagg(b.product, ', ') within group (order by b.product) as sub_product
from products a
join products b
on a.customer = b.customer
where a.priority = 1
and b.priority = 2
group by a.customer, a.product
即找到优先级为1的所有内容,使用它来生成两行,然后获得优先级为2的所有内容并将其汇总.
i.e. find everything that has a priority of 1, use this to generate your two rows and then get everything with a priority of 2 and aggregate those.
这篇关于Oracle字符串聚合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!