直接sum的话的确是你说的这样,但是我只想计算一次,4+(-4)和3+(-1), sum(PQ)/count(ITEM1)*2 as PQ 这样写的话虽然能达到要的结果, 但是PQ可能有三种值的情况,就不对了,暂时没想到好方法
刚才查了一下发现实际正确的结果应该是 ITEM1 ITEM2 STORE VEND A B C D PQ 02250046-850 845863 381001 121094 0 -3 -3 0 0 250016-196 822101 381001 121094 0 -3 -1 0 2上面给出的原始数据,因为PQ的多值情况导致QT也重复了,也就是说,PQ是2个值的话,每个ITEM号会重复两次,3个值的话就会重复三次。这样汇总的话就重复计算了。我也试过先将PQ除外,汇总其他的字段,然后单独汇总PQ,在将他们inner join 起来,结果到对了,但是性能不好。 唉,悲剧啊
declare @test table(ITEM1 nvarchar(20), ITEM2 varchar(20),STORE varchar(10), VEND varchar(10),QT int,DRTJ int ,PQ int)insert @test select '02250046-850','845863','381001','121094','-1','109341','4' union all select '02250046-850','845863','381001','121094','-1','109341','-4' union all select '02250046-850','845863','381001','121094','-1','109358','4' union all select '02250046-850','845863','381001','121094','-1','109358','-4' union all select '02250046-850','845863','381001','121094','-1','109358','4' union all select '02250046-850','845863','381001','121094','-1','109358','-4' union all select '02250046-850','845863','381001','121094','-1','109321','4' union all select '02250046-850','845863','381001','121094','-1','109321','-4' union all select '02250046-850','845863','381001','121094','-1','109321','4' union all select '02250046-850','845863','381001','121094','-1','109321','-4' union all select '02250046-850','845863','381001','121094','-1','109321','4' union all select '02250046-850','845863','381001','121094','-1','109321','-4' union all select '250016-196','822101','381001','121094','-1','109321','3' union all select '250016-196','822101','381001','121094','-1','109321','-1' union all select '250016-196','822101','381001','121094','-1','109341','3' union all select '250016-196','822101','381001','121094','-1','109341','-1' union all select '250016-196','822101','381001','121094','-1','109358','3' union all select '250016-196','822101','381001','121094','-1','109358','-1' union all select '250016-196','822101','381001','121094','-1','109358','3' union all select '250016-196','822101','381001','121094','-1','109358','-1' declare @n int set @n=109388 select a.ITEM1,a.ITEM2,a.STORE,a.VEND, sum(case when a.DRTJ>@n-30 and a.DRTJ<=@n then a.QT else 0 end) as A, sum(case when a.DRTJ>@n-60 and a.DRTJ<=@n-30 then a.QT else 0 end) as B, sum(case when a.DRTJ>@n-90 and a.DRTJ<=@n-60 then a.QT else 0 end) as C, sum(case when a.DRTJ>@n-120 and a.DRTJ<=@n-90 then a.QT else 0 end) as D, (select sum(distinct b.PQ) from @test as b where a.item1 = b.item1) as PQ from @test as a group by a.ITEM1,a.ITEM2,a.STORE,a.VEND 结果 02250046-850 845863 381001 121094 0 -6 -6 0 0 250016-196 822101 381001 121094 0 -6 -2 0 2
应该是4+(-4)和3+(-1)
上面的写法将PQ重复了
第一个0 是4-4+4-4+4-4+4-4得到的,不是4-4.
sum(PQ)/count(ITEM1)*2 as PQ 这样写的话虽然能达到要的结果,
但是PQ可能有三种值的情况,就不对了,暂时没想到好方法
ITEM1 ITEM2 STORE VEND A B C D PQ
02250046-850 845863 381001 121094 0 -3 -3 0 0
250016-196 822101 381001 121094 0 -3 -1 0 2上面给出的原始数据,因为PQ的多值情况导致QT也重复了,也就是说,PQ是2个值的话,每个ITEM号会重复两次,3个值的话就会重复三次。这样汇总的话就重复计算了。我也试过先将PQ除外,汇总其他的字段,然后单独汇总PQ,在将他们inner join 起来,结果到对了,但是性能不好。
唉,悲剧啊
declare @test table(ITEM1 nvarchar(20), ITEM2 varchar(20),STORE varchar(10), VEND varchar(10),QT int,DRTJ int ,PQ int)insert @test
select '02250046-850','845863','381001','121094','-1','109341','4' union all
select '02250046-850','845863','381001','121094','-1','109341','-4' union all
select '02250046-850','845863','381001','121094','-1','109358','4' union all
select '02250046-850','845863','381001','121094','-1','109358','-4' union all
select '02250046-850','845863','381001','121094','-1','109358','4' union all
select '02250046-850','845863','381001','121094','-1','109358','-4' union all
select '02250046-850','845863','381001','121094','-1','109321','4' union all
select '02250046-850','845863','381001','121094','-1','109321','-4' union all
select '02250046-850','845863','381001','121094','-1','109321','4' union all
select '02250046-850','845863','381001','121094','-1','109321','-4' union all
select '02250046-850','845863','381001','121094','-1','109321','4' union all
select '02250046-850','845863','381001','121094','-1','109321','-4' union all
select '250016-196','822101','381001','121094','-1','109321','3' union all
select '250016-196','822101','381001','121094','-1','109321','-1' union all
select '250016-196','822101','381001','121094','-1','109341','3' union all
select '250016-196','822101','381001','121094','-1','109341','-1' union all
select '250016-196','822101','381001','121094','-1','109358','3' union all
select '250016-196','822101','381001','121094','-1','109358','-1' union all
select '250016-196','822101','381001','121094','-1','109358','3' union all
select '250016-196','822101','381001','121094','-1','109358','-1' declare @n int
set @n=109388
select a.ITEM1,a.ITEM2,a.STORE,a.VEND,
sum(case when a.DRTJ>@n-30 and a.DRTJ<=@n then a.QT else 0 end) as A,
sum(case when a.DRTJ>@n-60 and a.DRTJ<=@n-30 then a.QT else 0 end) as B,
sum(case when a.DRTJ>@n-90 and a.DRTJ<=@n-60 then a.QT else 0 end) as C,
sum(case when a.DRTJ>@n-120 and a.DRTJ<=@n-90 then a.QT else 0 end) as D,
(select sum(distinct b.PQ) from @test as b where a.item1 = b.item1) as PQ
from @test as a
group by a.ITEM1,a.ITEM2,a.STORE,a.VEND
结果
02250046-850 845863 381001 121094 0 -6 -6 0 0
250016-196 822101 381001 121094 0 -6 -2 0 2
给出的原始数据,因为PQ的多值情况导致QT也重复了,也就是说,PQ是2个值的话,每个ITEM号会重复两次,3个值的话就会重复三次。这样汇总的话就重复计算了。上面的正确汇总应该是:
02250046-850 845863 381001 121094 0 -3 -3 0 0
250016-196 822101 381001 121094 0 -3 -1 0 2