sql将产品所有者的表与具有主要所有者和级联所有者的列组合在一起 [英] sql to group table of product owners with a column for primary owner and concatenated secondary owners

问题描述

我有一个与他们的主人产品表。每个所有者都在自己的行中，并且拥有主要类型或次要类型的所有者类型。并非每个产品都有第二个所有者。

我需要按产品分组，将一个表中的主要所有者和所有次要所有者连接在一个列中。如果一个产品有多个主要所有者，它应该选择第一个，其余的则转到第二个所有者。如果产品没有主要所有者，则应该选择第一个/任何第二个所有者。

这是一个输入表：

  + --------- + ------------ + --------- -  + 
 |产品|所有者类型|所有者| 
 + --------- + ------------ + ---------- + 
 | a | primary | one | 
 | a |次| |两个| 
 | a |次| |三| | 
 | b | primary |四| | 
 | b |次| |五| 
 | c | primary |六| 
 | d |次| |七| 
 | e |次| |八| 
 | e |次| |九| 
 | f | primary |十| 
 | f | primary |十一| | 
 | f |次| |十二
 | f |次| |十三
 + --------- + ------------ + ---------- + 
  
 
 
 
预期结果是：
 
 $ $ p $  +  - ------- + -------- + -------------------------- + 
 |产品|主要所有者|次要业主| 
 + --------- + --------------- + ------------------- ------- + 
 | a | one |二，三| 
 | b |四| |五| 
 | c |六| | 
 | d |七| | 
 | e |八|九| 
 | f |十|十一，十二，十三| 
 + --------- + --------------- + ------------------- ------- + 
  

如果您注意到产品 d 和 e 没有主要所有者，因此它会选择第一个所有者，然后再次将其包含在次要所有者列中。类似于产品 f ，它有两个主要所有者。 我知道如何 group 按产品分类并使用 FOR XML PATH 来连接行/字段。在组中，我知道如何选择第一个产品，其中所有者类型是主要 code>。我无法弄清楚的是，选择第一个主要所有者并从第二个所有者列中排除它和/或在没有主要所有者的情况下选择第一个所有者并将其从第二所有者列中排除的逻辑。 / p>



我甚至不知道从哪里开始SQL。

有什么想法？

解决方案

执行此操作的一种方法是分配优先处理owner_type ='Primary'行的行号。然后获取第一行作为主要所有者， group_concat 其他人将成为次要所有者。

 ，max（当rnum = 1，然后所有者结束时）作为primary_owner 
，group_concat（当rnum<>时，则所有者结束顺序为rnum时）作为secondary_owners 
 from（select product，owner_type，owner，
 @rn：= @ prev_product = product时的情况然后@ rn + 1 else 1结束为rnum，
 @prev_product：=产品
 from tablename 
交叉连接（选择@rn：= 0，@ prev_product：=''，@ prev）r 
按产品排序，owner_type ='主要'，所有者
）t 
 group by product 
 order by 1 
  

I have a table of products with their owners. Each owner is in their own row and has an owner type of either primary or secondary. Not every product has a secondary owner.

I need to get a table grouped by product with the primary owner in one column and all the secondary owners concatenated in a second column. If a product has multiple primary owners it should select the first one and the rest go to the secondary owners. If a product doesn't have a primary owner then it should just select the first/any secondary owner.

This is an input table:

+---------+------------+----------+
| Product | Owner Type |  Owner   |
+---------+------------+----------+
| a       | primary    | one      |
| a       | secondary  | two      |
| a       | secondary  | three    |
| b       | primary    | four     |
| b       | secondary  | five     |
| c       | primary    | six      |
| d       | secondary  | seven    |
| e       | secondary  | eight    |
| e       | secondary  | nine     |
| f       | primary    | ten      |
| f       | primary    | eleven   |
| f       | secondary  | twelve   |
| f       | secondary  | thirteen |
+---------+------------+----------+

The expected result is:

+---------+---------------+--------------------------+
| Product | Primary Owner |     Secondary Owners     |
+---------+---------------+--------------------------+
| a       | one           | two, three               |
| b       | four          | five                     |
| c       | six           |                          |
| d       | seven         |                          |
| e       | eight         | nine                     |
| f       | ten           | eleven, twelve, thirteen |
+---------+---------------+--------------------------+

If you notice, products d and e don't have a primary owner so it picks the first secondary owner and then doesn't include it again in the secondary owner column. Similar for product f that has two primary owner.

I know how to group by product and use FOR XML PATH to concatenate rows/fields. In the group I know how to select the first product where Owner Type is primary. What I cannot figure out is the logic that would be needed to pick the first primary owner and exclude it from the secondary owner column and/or select the first secondary owner if there is no primary owner and exclude it from the secondary owner column.

I don't even know where to start with the SQL.

Any ideas?

解决方案

One way to do this is assigning row numbers prioritizing owner_type='Primary' rows. Then get the first row as the primary owner and group_concat others to be secondary owners.

select product
,max(case when rnum=1 then owner end) as primary_owner
,group_concat(case when rnum<>1 then owner end order by rnum) as secondary_owners
from (select product,owner_type,owner,
      @rn:=case when @prev_product=product then @rn+1 else 1 end as rnum,
      @prev_product:=product 
      from tablename 
      cross join (select @rn:=0,@prev_product:='',@prev) r
      order by product,owner_type='Primary',owner
     ) t
group by product
order by 1

Sample Demo

这篇关于sql将产品所有者的表与具有主要所有者和级联所有者的列组合在一起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！