如何将一个客户的一张表中的两个不同值合并为一行 [英] how to combine two different values from one table of one customer into one row

问题描述

我想列出订购了不同代码的ID.我不想列出仅订购一个代码的ID(即ID 4、5).

I want to list out the ID's that have ordered different codes. I dont want to list ID which ordered only one code(i.e. ID 4, 5).

ID      product      code
1       Apple        145
1       Grapes       146
2       Orange       147
2       Apple        145
2       Plum         148 
3       Grapes       146
3       Orange       147
4       Grapes       146
5       Orange       147

我希望它看起来像这样

ID      Codes       
1     145 | 146
2     147 | 145 | 148
3     146 | 147      

感谢任何帮助！

推荐答案

您需要GROUP BY id，并且多个订单"上的条件将包含在HAVING子句中(因为这是对每个组的约束，而不是输入数据中的每一行).聚合是通过LISTAGG完成的.

You need to GROUP BY id, and the condition on "more than one order" goes into a HAVING clause (because it is a constraint on each group, not on each individual row in the input data). The aggregation is done with LISTAGG.

with
     test_data ( id, product, code ) as (
       select 1, 'Apple' , 145 from dual union all
       select 1, 'Grapes', 146 from dual union all
       select 2, 'Orange', 147 from dual union all
       select 2, 'Apple' , 145 from dual union all
       select 2, 'Plum'  , 148 from dual union all
       select 3, 'Grapes', 146 from dual union all
       select 3, 'Orange', 147 from dual union all
       select 4, 'Grapes', 146 from dual union all
       select 5, 'Orange', 147 from dual
     )
--  End of test data (not part of the solution). Query begins below this line.
select   id, listagg(code, ' | ') within group (order by id) as codes
from     test_data
group by id
having   count(*) > 1
;

ID  CODE
--  ---------------
 1  145 | 146
 2  145 | 147 | 148
 3  146 | 147

但是，在Oracle 10中您没有LISTAGG().在Oracle 11.2之前，获得相同结果的一种常用方法是使用分层查询，如下所示:

However, in Oracle 10 you don't have LISTAGG(). Before Oracle 11.2, a common way to get the same result was to use hierarchical queries, something like below:

select id, ltrim(sys_connect_by_path(code, ' | '), ' | ') as codes
from   (
         select id, code,
                row_number() over (partition by id order by code) as rn
         from   test_data
       )
where connect_by_isleaf = 1 and level > 1
connect by rn = prior rn + 1
       and prior id = id
       and prior sys_guid() is not null
start with rn = 1
;

已编辑:

如果首先需要区分"相同ID的重复CODE，然后-使用第二种解决方案-在最里面的子查询中都需要进行以下更改:

If repeated CODE for the same ID need to be "distincted" first, then - using the second solution - the following changes are needed, both in the innermost subquery:

• 将SELECT ID, CODE, ...更改为SELECT DISTINCT ID, CODE, ...

将ROW_NUMBER()更改为DENSE_RANK()

这篇关于如何将一个客户的一张表中的两个不同值合并为一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！