我正在创建一个有奇怪情况的报告:
我有一张表格如下:
原因
ReasonID | ReasonName |
---|---|
1 | 烧伤 |
2 | 焊接烧伤 |
3 | 点烧伤 |
select container,case
when container='A' Then sum(Defectqty)
when container='B' Then sum(Defectqty)
end as Defectqty
from container group by container
您可以使用派生表尝试下面的sql查询。你不需要加入。您所需要的全部都可以在一个表中获得:Container。
declare @table table(container char(1), reasonName varchar(30), defectqty int)
insert into @table values
('A','Burn', 4),
('A','Weld Burn', 10),
('B','Spot Burn', 15)
SELECT container, reasonName, total_defectqty from
(select *, sum(defectqty) over (partition by container) as total_defectqty from @table) as t
WHERE ReasonName = 'Burn' and container = 'A'
容器 | reasonName | 总影响数量|
---|---|---|
A | 烧伤 | 14 |