Oracle 字符串聚合

2021-12-30 00:00:00 sql oracle11g 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

SQL 小提琴

每个客户，PRODUCT 列假设每个客户都有一个主要产品，然后按优先顺序获得第一个产品.第二列只采用优先级为 2 的地方并使用字符串连接函数 LISTAGG() 将您的值连接在一起.

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.

相关文章