我需要按产品分组，将一个表中的主要所有者和所有次要所有者连接在一个列中。如果一个产品有多个主要所有者，它应该选择第一个，其余的则转到第二个所有者。如果产品没有主要所有者，则应该选择第一个/任何第二个所有者。
这是一个输入表：
+ --------- + ------------ + --------- - + |产品|所有者类型|所有者| + --------- + ------------ + ---------- + | 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 其他人将成为次要所有者。
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 1Sample Demo
更多推荐
sql将产品所有者的表与具有主要所有者和级联所有者的列组合在一起
发布评论