在Oracle中将两行放在同一行中

Question

让我们以这个表为例，它将产品与他从客户反馈中获得的星数关联起来(它恰好是按星数排序的)：

| ProductID |  Stars  | 
|-----------|---------|
|    23     |    10   |
|    12     |    10   |
|    17     |     9   |
|     5     |     8   |
|    20     |     8   |
|    18     |     7   |  

如何选择(在同一行上显示)成对的产品in？

如下所示：

|  Product1 |   Product2  | 
|-----------|-------------|
|    23     |      12     |
|    17     |       5     |
|    20     |      18     |  

或者像这样：

|     Products     | 
|------------------|
|    23     12     |
|    17      5     |
|    20     18     |

Answer 1

不清楚为什么您在输出中选择20之前的5；请记住，表中的行是没有排序的。在我的解决方案中，我先按星号排序，然后按productid排序；如果您的行也按其他值排序，则可以使用其他值。

如果要一次对7个值进行分组，可以将除法中的2到7更改为2。

提供此功能只是为了说明可以在Oracle SQL中(在数据库中)完成此操作。它不应该，但这是由您决定的。

with
     inputs ( productid, stars) as ( 
       select 23, 10 from dual union all
       select 12, 10 from dual union all
       select 17,  9 from dual union all
       select  5,  8 from dual union all
       select 20,  8 from dual union all
       select 18,  7 from dual
     )
-- end of test data, solution begins below
select listagg(productid, ' ') within group (order by rn) as result
from ( select productid, stars,
              row_number() over (order by stars desc, productid desc) as rn
       from   inputs
     )
group by ceil(rn/2)
order by ceil(rn/2)
;

RESULT
------
23 12
17 20
5 18

Answer 2

WITH t
     AS (SELECT 23 product_id, 10 stars FROM DUAL
         UNION ALL
         SELECT 12, 10 FROM DUAL
         UNION ALL
         SELECT 17, 9 FROM DUAL
         UNION ALL
         SELECT 5, 8 FROM DUAL
         UNION ALL
         SELECT 20, 8 FROM DUAL
         UNION ALL
         SELECT 18, 7 FROM DUAL),
     t2
     AS (  SELECT product_id,
                  stars,
                    ROW_NUMBER () OVER (ORDER BY stars DESC)
                  + MOD (ROW_NUMBER () OVER (ORDER BY stars DESC), 2)
                     grp
             FROM t
         ORDER BY stars DESC)
  SELECT LISTAGG (product_id, ' ') WITHIN GROUP (ORDER BY stars DESC, ROWNUM)
            AS product_id
    FROM t2
GROUP BY grp

结果

23 12
17 5
20 18

Answer 3

使用listagg生成option2

select stars, listagg(ProductID, ' ') within group (order by ProductID) as Products
from Table1
group by Stars