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

Question

我有一个表，它可能有相同的项目，但有不同的尺寸，我想计算那些有多个尺寸的项目(例如，S，M尺寸的漫威衬衫将算作"1")，但仍然可以计算有多少S和M。我有两个结果想要得到。请参阅下面的详细信息。

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

在这种情况下，下面是另一个可能的预期结果：如果有多种尺寸的衬衫不应该单独计算(即漫威衬衫有多种尺寸，因此它不会计算S或M，因为它已经在multiple_Size下计算)，该怎么办？

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

Answer 1

您可能需要按商品编号分组两次(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            |

Answer 2

--creando tabla
create table #temp (itemId int, size nvarchar(1))
--insertando valores
insert into #temp values (1,'S')
insert into #temp values (1,'M')
insert into #temp values (2,'M')
insert into #temp values (3,'L')
insert into #temp values (4,'M')

-- table of Different Item Codes
select 
itemId
into #masDeUnItem
from
(select itemId,size from #temp group by itemId,size) t1
group by itemId 
having count(1) > 1

-- Variable of Counting different Items
declare @itemsDistintos int 

-- Providing Value to Variable
select @itemsDistintos = count(1) from 
(
select * from #masDeUnItem
) t1

--Outcome 1
select count(distinct(itemId)) TOTAL
,
sum(case when size = 'S' then 1 else 0 end) SMALL
, sum(case when size = 'M' then 1 else 0 end) MEDIUM
, sum(case when size = 'L' then 1 else 0 end) LARGE
,  @itemsDistintos as Multiple_Sizes
from #temp

--Outcome 2
select count(distinct(a.itemId)) TOTAL
,
sum(case when size = 'S' and b.itemId is null then 1 else 0 end) SMALL
, sum(case when size = 'M' and b.itemId is null then 1 else 0 end) MEDIUM
, sum(case when size = 'L' and b.itemId is null then 1 else 0 end) LARGE
,  @itemsDistintos as Multiple_Sizes
from #temp a 
left join #masDeUnItem b 
on a.itemId = b.itemId