declare @test table(n int, item nvarchar(10))
insert @test select 0,'A001' union all
select 100,'B001' union all
select 100,'C001' union all
select 100,'D001' union all
select 0,'E001' union all
select 0,'F001' union all
select -1,'I001' union all
select -1,'J001' n的值只有100,0和-1三种情况
需要sum(n<>-1)/count(n<>-1)
只汇总n不等于-1的值然后求平均值
然后放在第三列
结果如下
n item result
0 A001 50
100 B001 50
100 C001 50
100 D001 50
0 E001 50
0 F001 50
-1 I001 50
-1 J001 50
declare @test table(n int, item nvarchar(10))
insert @test select 0,'A001' union all
select 100,'B001' union all
select 100,'C001' union all
select 100,'D001' union all
select 0,'E001' union all
select 0,'F001' union all
select -1,'I001' union all
select -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 50
100 B001 50
100 C001 50
100 D001 50
0 E001 50
0 F001 50
-1 I001 50
-1 J001 50(8 行受影响)
*/
insert @test select 0,'A001' union all
select 100,'B001' union all
select 100,'C001' union all
select 100,'D001' union all
select 0,'E001' union all
select 0,'F001' union all
select -1,'I001' union all
select -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 50
0 A001 50
0 E001 50
0 F001 50
100 B001 50
100 C001 50
100 D001 50(8 行受影响)
改成declare @test table(n int, item nvarchar(10))
insert @test select 0,'A001' union all
select 100,'B001' union all
select 100,'C001' union all
select 100,'D001' union all
select 0,'E001' union all
select 0,'F001' union all
select -1,'I001' union all
select -1,'J001' select * ,(select sum(n) from @test where n<>-1)/(select count(*) from @test where n<>-1)as result from @test(8 行受影响)
n item result
----------- ---------- -----------
0 A001 50
100 B001 50
100 C001 50
100 D001 50
0 E001 50
0 F001 50
-1 I001 50
-1 J001 50(8 行受影响)
*,
(select sum(n) from @test where n<>-1)/(select count(1) from @test where n<>-1) as result
from
@test
declare @cunt int
declare @su int
insert @test select 0,'A001' union all
select 100,'B001' union all
select 100,'C001' union all
select 100,'D001' union all
select 0,'E001' union all
select 0,'F001' union all
select -1,'I001' union all
select -1,'J001'
set @cunt=(select count(*) from @test where n<>-1)
set @su=(select sum(n) from @test where n<>-1)
select * ,( @su/@cunt)as result from @test
(8 行受影响)
n item result
----------- ---------- -----------
0 A001 50
100 B001 50
100 C001 50
100 D001 50
0 E001 50
0 F001 50
-1 I001 50
-1 J001 50(8 行受影响)