汇总有关问题

汇总问题

SQL code

declare @test table(n int, item nvarchar(10))insert @test select 0,'A001' union allselect 100,'B001' union allselect 100,'C001' union allselect 100,'D001' union allselect 0,'E001' union allselect 0,'F001' union allselect -1,'I001' union allselect -1,'J001' n的值只有100,0和-1三种情况需要sum(n<>-1)/count(n<>-1)只汇总n不等于-1的值然后求平均值然后放在第三列结果如下n    item     result0    A001     50100    B001     50100    C001     50100    D001     500    E001     500    F001     50-1    I001     50-1    J001     50

SQL code

declare @test table(n int, item nvarchar(10))insert @test select 0,'A001' union allselect 100,'B001' union allselect 100,'C001' union allselect 100,'D001' union allselect 0,'E001' union allselect 0,'F001' union allselect -1,'I001' union allselect -1,'J001' select *,result=(select sum(n) from @test where n<>-1)/(select count(*) from @test where n<>-1) from @test/*n           item       result----------- ---------- -----------0           A001       50100         B001       50100         C001       50100         D001       500           E001       500           F001       50-1          I001       50-1          J001       50(8 行受影响)*/
[解决办法]
SQL code
declare @test table(n int, item nvarchar(10)) insert @test select 0,'A001' union allselect 100,'B001' union allselect 100,'C001' union allselect 100,'D001' union allselect 0,'E001' union allselect 0,'F001' union allselect -1,'I001' union allselect -1,'J001' select * ,(select sum(n) from @test where n<>-1)/(select count(*) from @test where n<>-1)as result from  @test group by n,item(8 行受影响)n           item       result----------- ---------- ------------1          I001       50-1          J001       500           A001       500           E001       500           F001       50100         B001       50100         C001       50100         D001       50(8 行受影响)
[解决办法]
SQL code
select *, (select sum(n) from @test where n<>-1)/(select count(1) from @test where n<>-1) as resultfrom @test