oracle 如果产品超过3个，如何在新行中显示产品

p8h8hvxi 于 2023-03-07 发布在 Oracle

关注(0)|答案(1)|浏览(123)

我想显示员工的名字、姓氏和产品详细信息。
如果乘积大于3，则应显示在新行中。
样本数据：

Employee first name: Emma
Employee last name: Snow
Product 1:Apple
Product 1 Amount:100
Product 2:Banana
Product 2:50
Product 3:Guava
Product 3 amount:40
Product 4:watermelon
Product 4 amount:60
Product 5: Melon
Product 5 amount:30

现在由于乘积大于3，所以预期结果

is:
employeefname employeelname product1   product1amount product2 product2amount product3 product3maount
------------- ------------- ---------- -------------- -------- -------------- -------- --------------
Emma          Snow          Apple      100            Banana   50             Guava    40
Emma          Snow          Watermelon 60             Melon    30

因为西瓜和甜瓜是产品4和产品5，所以它们应该与p1、p2、p3在同一列下，但是在新的行中。
请帮助我如何才能做到这一点。
先谢了。
这是我创建的查询。

SELECT emp.first_name emp_fname,
         emp.last_name,
         emp_lname,
         MIN (p1_product_name) p1_product_name,
         SUM (ROUND (p1_product_amount)) p1_product_amount,
         MIN (p2_product_name) p2_product_name,
         SUM (ROUND (p2_product_amount)) p2_product_amount,
         MIN (p3_product_name) p3_product_name,
         SUM (ROUND (p3_product_amount)) p3_product_amount
    FROM employee emp,
         (SELECT employee_id,
                 customer_id,
                 (SELECT product_name
                    FROM DUAL
                   WHERE MOD (rno, 3) = 1) p1_product_name,
                 (SELECT product_amount
                    FROM DUAL
                   WHERE MOD (rno, 3) = 1) p1_product_amount,
                 (SELECT product_name
                    FROM DUAL
                   WHERE MOD (rno, 3) = 2) p2_product_name,
                 (SELECT product_amount
                    FROM DUAL
                   WHERE MOD (rno, 3) = 2) p2_product_amount,
                 (SELECT product_name
                    FROM DUAL
                   WHERE MOD (rno, 3) = 3) p3_product_name,
                 (SELECT product_amount
                    FROM DUAL
                   WHERE MOD (rno, 3) = 3) p3_product_amount
            FROM (SELECT employee_id,
                         customer_id,
                         product_name,
                         product_amount,
                         ROW_NUMBER ()
                            OVER (
                               PARTITION BY employee_id,
                                            customer_id,
                                            meeting_id
                               ORDER BY employee_id, customer_id, meeting_id) AS rno
                    FROM (SELECT DISTINCT rpt.employee_id,
                                          rpt.meeting_id,
                                          rpt.customer_id,
                                          state_expense_purpose_desc,
                                          rpt.default_amount,
                                          prd.product_name,
                                          rpt.amount product_amount
                            FROM state_customer_expense rpt
                                 JOIN product prd
                                    ON rpt.product_id = prd.product_id
                           WHERE rpt.state_period_id = 10001001210627)
                         PIVOT (SUM (default_amount) AS sum_amount
                               FOR state_expense_purpose_desc
                               IN ('1' AS p1, '2' AS p2, '3' AS p3)))) data
   WHERE emp.employee_id = data.employee_id
GROUP BY emp.first_name, emp.last_name

oracle

来源：https://stackoverflow.com/questions/75647181/how-to-show-products-in-a-new-row-if-the-products-are-more-than-3

1条答案

按热度按时间

polhcujo1#

您可以使用ROW_NUMBER分析函数对行进行编号，然后，对于每3个项目，您可以将PIVOT行转换为列：

SELECT first_name,
       last_name,
       p1_product_name,
       p1_product_amount,
       p2_product_name,
       p2_product_amount,
       p3_product_name,
       p3_product_amount
FROM   (
  SELECT e.employee_id,
         e.first_name,
         e.last_name,
         p.product_name,
         p.product_amount,
         FLOOR(
           ( ROW_NUMBER() OVER (
               PARTITION BY e.employee_id, x.customer_id, x.meeting_id
               ORDER BY ROWNUM
             ) - 1
           ) / 3
         ) AS row_no,
         MOD(
           ROW_NUMBER() OVER (
             PARTITION BY e.employee_id, x.customer_id, x.meeting_id
             ORDER BY ROWNUM
           ) - 1,
           3
         ) AS item_no
  FROM   employee e
         INNER JOIN state_customer_expense x
         ON (e.employee_id = x.employee_id)
         INNER JOIN product p
         ON (x.product_id = p.product_id)
  WHERE  x.state_period_id = 10001001210627
)
PIVOT (
  MAX(product_name) AS product_amount,
  MAX(product_amount) AS product_name
  FOR item_no IN (
    0 AS p1,
    1 AS p2,
    2 AS p3
  )
);

其中，对于示例数据：

CREATE TABLE employee (employee_id, first_name, last_name) AS
SELECT 1, 'Emma', 'Snow' FROM DUAL;

CREATE TABLE state_customer_expense (employee_id, product_id, state_period_id, meeting_id, customer_id) AS
SELECT 1, LEVEL, 10001001210627, 1, 1 FROM DUAL CONNECT BY LEVEL <= 5;

CREATE TABLE product (product_id, product_name, product_amount) AS
SELECT 1, 'Apple',      100 FROM DUAL UNION ALL
SELECT 2, 'Banana',      50 FROM DUAL UNION ALL
SELECT 3, 'Guava',       40 FROM DUAL UNION ALL
SELECT 4, 'watermelon',  60 FROM DUAL UNION ALL
SELECT 5, 'Melon',       30 FROM DUAL;

输出：
| 名字|姓氏|P1产品名称|P1_产品_金额|P2产品名称|P2_产品_金额|P3产品名称|P3_产品_金额|
| - ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|
| 艾玛|雪|一百|苹果|五十|香蕉|四十|Guava|
| 艾玛|雪|六十|西瓜|三十|甜瓜|* 无效 | 无效 *|
fiddle

赞(0）回复(0）举报 2023-03-07

我来回答

oracle 如果产品超过3个，如何在新行中显示产品

1条答案

相关问题

热门标签

最新问答