时间戳

首页 > 解决方案 > Oracle SQL 行到具有分组依据和总和的列

sql - Oracle SQL 行到具有分组依据和总和的列

问题描述

我有一张包含以下数据的表格。

ID    Name     DiscountGroup Discount DicountCategory
A     XXX      G025          25        MANGMNT
A     XXX      T005           5        HIGH GPA
A     XXX      T010          10        
B     XXXB     G040          40        SOMETXT
B     XXXB     T005           5        HIGH GPA

我想将数据显示为

ID    Name     PermanentDiscount Cateogry TempDiscount 
A     XXX      25                MANGMNT  15
B     XXXB     40                SOMETXT   5

即 1 行中的永久和临时折扣。所有永久折扣均以 GXXX 开头，后跟 3 位数。同样，所有临时折扣都以 TXXX 开头，后跟 3 位数字，其中 XXX 表示实际折扣。一个ID可以有多个临时折扣，但只能有一个永久折扣。我尝试使用

SELECT *
  FROM tableA
PIVOT (
  MAX(Discount) FOR DiscountGroup IN ('')
);

但我无法弄清楚列表中的值。

标签： sqloraclepivotpivot-table

解决方案

这应该有效：

WITH sample_data AS 
   ( SELECT 'A' AS id, 'XXX' AS name, 'G025' AS DiscountGroup, 25 AS Discount, 'MANGMNT' AS DiscountCategory FROM DUAL UNION ALL
     SELECT 'A' AS id, 'XXX' AS name, 'T005' AS DiscountGroup, 5 AS Discount, 'HIGH GPA' AS DiscountCategory FROM DUAL UNION ALL
     SELECT 'A' AS id, 'XXX' AS name, 'T010' AS DiscountGroup, 10 AS Discount, NULL AS DiscountCategory FROM DUAL UNION ALL
     SELECT 'B' AS id, 'XXXB' AS name, 'G040' AS DiscountGroup, 40 AS Discount, 'SOMETXT' AS DiscountCategory FROM DUAL UNION ALL
     SELECT 'B' AS id, 'XXXB' AS name, 'T005' AS DiscountGroup, 5 AS Discount, 'HIGH GPA' AS DiscountCategory FROM DUAL
   )
SELECT id,
       name,
       MAX(CASE WHEN DiscountGroup = 'G' THEN Discount ELSE 0 END) AS PermanentDiscount,
       MAX(CASE WHEN DiscountGroup = 'G' THEN Cateogry ELSE NULL END) AS Cateogry,
       SUM(CASE WHEN DiscountGroup = 'T' THEN Discount ELSE 0 END) AS TempDiscount
  FROM ( SELECT id,
                name,
                SUBSTR(DiscountGroup, 1, 1) AS DiscountGroup,
                Discount,
                FIRST_VALUE(DiscountCategory) OVER(PARTITION BY id, name, SUBSTR(DiscountGroup, 1, 1) ORDER BY Discount DESC) AS Cateogry
           FROM sample_data
       )
  GROUP BY id, name
  ORDER BY id, name;

结果：

I NAME PERMANENTDISCOUNT CATEOGRY TEMPDISCOUNT
- ---- ----------------- -------- ------------
A XXX                 25 MANGMNT            15
B XXXB                40 SOMETXT             5

推荐阅读