时间戳

首页 > 解决方案 > 如何使用具有特定条件的 case 语句总结唯一值

sql - 如何使用具有特定条件的 case 语句总结唯一值

问题描述

我有一张桌子，可能有相同的物品，但尺寸不同，我想计算不止一种尺寸的物品（例如，带有 S、M 尺寸的奇迹衬衫将计为“1”），但仍然可以计算多少个 S 和 M。我想要得到 2 个结果。请参阅下文了解更多详情。

TABLE B   

ITEM_NO ITEM             
=========================
3130C   MARVEL_SHIRT     
1845C   SPONGEBOB_BOXERS 
A900C   CK_COAT          
A988C   RIDER_JEANS      


TABLE C

ITEM_NO SIZE          
===============
3130C   S             
3130C   M             
1845C   M             
A900C   L             
A988C   M     -

我试着只计算它，但它不正确，因为它计算了有多少不同的尺寸

select (case substr(item_no, 5, 1)
            when 'C' then 'clothes'
            when 'T' then 'toys'
            else 'misc' 
        end) inv, 
       count(item_no) total 
       ,sum (case when C.size = 'S' then 1 else 0 end) AS small
       ,sum (case when C.size = 'M' then 1 else 0 end) AS med
       ,sum (case when C.size = 'L' then 1 else 0 end) AS large   
       ,count (distinct C.size) AS multiple_sizes
        from B left outer join C on B.item_no = C.item_no 
        group by substr(item_no, 5, 1);

实际结果（不正确）：

INV     TOTAL   Small   Med   Large   Multiple_Sizes
==========================================================
CLOTHES    4       1    3       1       3

期望/预期结果：

INV     TOTAL   Small   Med   Large   Multiple_Sizes
==========================================================
CLOTHES    4       1    3       1       1

在这种情况下，下面是另一个可能的期​​望结果：如果不应该单独计算具有多个尺寸的那些（即 Marvel 衬衫有多个尺寸，因此它不会计算 S 或 M，因为它已经被计算在 Multiple_Sizes 下）？

INV     TOTAL   Small   Med   Large     Multiple_Sizes
==========================================================
CLOTHES    4       0    2     1        1

标签： sqloraclegroup-byconditional-aggregation

解决方案

您可能需要按项目编号 (2) 按项目类别分组两次 (1)：

SELECT SUBSTR(item_no, 5, 1) AS category
     , COUNT(*) AS count_products
     , SUM(count_small) AS small
     , SUM(count_med) AS med
     , SUM(count_large) AS large
     , SUM(CASE WHEN count_small + count_med + count_large > 1 THEN 1 END) AS has_multiple
FROM (
    SELECT b.ITEM_NO
         , COUNT(CASE WHEN c.SIZE = 'S' THEN 1 END) AS count_small
         , COUNT(CASE WHEN c.SIZE = 'M' THEN 1 END) AS count_med
         , COUNT(CASE WHEN c.SIZE = 'L' THEN 1 END) AS count_large
    FROM b
    LEFT JOIN c ON b.item_no = c.item_no
    GROUP BY b.ITEM_NO
) x
GROUP BY SUBSTR(item_no, 5, 1)

| category | count_products | small | med | large | has_multiple |
| C        | 4              | 1     | 3   | 1     | 1            |

和变化：

SELECT SUBSTR(item_no, 5, 1) AS category
     , COUNT(*) AS count_products
     , SUM(CASE WHEN count_small + count_med + count_large = 1 THEN count_small END) AS small
     , SUM(CASE WHEN count_small + count_med + count_large = 1 THEN count_med   END) AS med
     , SUM(CASE WHEN count_small + count_med + count_large = 1 THEN count_large END) AS large
     , SUM(CASE WHEN count_small + count_med + count_large > 1 THEN 1 END) AS has_multiple
FROM (
    SELECT b.ITEM_NO
         , COUNT(CASE WHEN c.SIZE = 'S' THEN 1 END) AS count_small
         , COUNT(CASE WHEN c.SIZE = 'M' THEN 1 END) AS count_med
         , COUNT(CASE WHEN c.SIZE = 'L' THEN 1 END) AS count_large
    FROM b
    LEFT JOIN c ON b.item_no = c.item_no
    GROUP BY b.ITEM_NO
) x
GROUP BY SUBSTR(item_no, 5, 1)

| category | count_products | small | med | large | has_multiple |
| C        | 4              | 0     | 2   | 1     | 1            |

推荐阅读