根据一列的值将一列求和为两个不同的列

Question

我需要的只是"storage "中的项目列表，但是结果集应该包括该项目在存储和活动位置的数量之和。

下面是一个数据集示例:

<表类> ID 项位置数量tbody><<tr>1ItemA存储42ItemA活动93ItemB存储34ItemB存储25ItemA活动16ItemC盒装37ItemD活动18ItemD存储1

使用WHERE子句只查看有问题的位置:

select
item,
sum(case when location = 'Storage' then qty else 0 end) as storage,
sum(case when location = 'Active' then qty else 0 end) as active
from items
where location in ('Storage', 'Active')
group by item
order by item;

更新你已经在请求中更改了所需的输出，并且只需要'Storage'中的项目。为此，只需添加HAVING子句，例如:

select
item,
sum(case when location = 'Storage' then qty else 0 end) as storage,
sum(case when location = 'Active' then qty else 0 end) as active
from items
where location in ('Storage', 'Active')
group by item
having sum(case when location = 'Storage' then qty else 0 end) > 0
order by item;

这应该会给您想要的结果。其他答案包括不在"存储"中的值

select 
item,
sum(case when location = 'Active' then qty else 0 end) as active_qty,
sum(case when location = 'Storage' then qty else 0 end) as storage_qty
from *table*
where item in (select item from *table* where location = 'Storage')
group by item
order by item;

Select  item,
SUM(Case When Location = 'Storage' THEN Qty else 0 END) AS Storage,
SUM(Case When Location = 'Active' THEN Qty else 0 END) AS Active 
from table1 
where location in ('Storage','Active')
GROUP BY Item

http://sqlfiddle.com/!9/7339b9a/8

您可以在子查询中使用WHERE子句来标识感兴趣的项，然后在聚合之前使用JOIN来过滤行

SELECT 
A.ITEMDESC, 
SUM(CASE WHEN A.LOCATION='Storage' THEN A.QTY ELSE 0 END), 
SUM(CASE WHEN A.LOCATION='Active'  THEN A.QTY ELSE 0 END)
FROM       
ITEMS A
INNER JOIN
(SELECT DISTINCT ITEMDESC FROM ITEMS WHERE LOCATION='Storage') B
ON      
A. ITEMDESC = B.ITEMDESC
GROUP BY   
A.ITEMDESC

或者您可以使用HAVING子句

过滤聚合后的行

SELECT 
ITEMDESC, 
SUM(CASE WHEN LOCATION='Storage' THEN QTY ELSE 0 END), 
SUM(CASE WHEN LOCATION='Active'  THEN QTY ELSE 0 END)
FROM       
ITEMS
GROUP BY   
ITEMDESC
HAVING
MAX(CASE WHEN LOCATION='Storage' THEN 1 ELSE 0 END) > 0