SQL Server 2000 开发版下测试的结果:declare @t table(num int) insert into @t select 92 union all select 34 union all select 15 union all select 0 union all select 78select avg(num) from @t/* ----------- 43 */
--> 生成测试数据: @T DECLARE @T TABLE (data INT) INSERT INTO @T SELECT 92 UNION ALL SELECT 34 UNION ALL SELECT 15 UNION ALL SELECT 0 UNION ALL SELECT 78--SQL查询如下:SELECT AVG(data*1.),SUM(data)*1./COUNT(*),SUM(data) FROM @T/* --------------------------------------- --------------------------------------- ----------- 43.800000 43.80000000000 219(1 行受影响)*/
AVG()会忽略NULL值的行,如果楼主那行的数据是NULL,则不会用于平均值计算。declare @t table(num int) insert into @t select 92 union all select 34 union all select 15 union all select null union all select 78select avg(num) from @t/* ----------- 54 */
if object_id('tb') is not null drop table tb go create table tb([col] int) insert tb select 92 insert tb select 34 insert tb select 15 insert tb select 0 insert tb select 78 go select sum(col)/sum(case col when 0 then 0 else 1 end) colavg from tb /* colavg ----------- 54(1 行受影响) */ select avg(col) colavg from tb /* colavg ----------- 43(1 行受影响) */
select sum(1.*col)/count(1) colavg from tb
if object_id('[tb]') is not null drop table [tb] go create table [tb]([val] int) insert [tb] select 92 union all select 34 union all select 15 union all select 0 union all select 78 goselect avg(val) from [tb] /* 43 */ select @@version /* Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) */ 2005 sp3下也是正常的。 一般avg会忽略null值,但不会忽略0值。 avg(val)等价于sum(val)/count(val),但不等价于sum(val)/count(*)。 在做统计时,可能需要用到后者。
计算了0行啊 219/5=43.8 测试数据: declare @T table(id int,num float) insert into @T select 1,92 union all select 2,34 union all select 3,15 union all select 4,0 union all select 5,78查询: select avg(num) as num from @T
insert into @t select 92 union all
select 34 union all
select 15 union all
select 0 union all
select 78select avg(num) from @t/*
-----------
43
*/
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (data INT)
INSERT INTO @T
SELECT 92 UNION ALL
SELECT 34 UNION ALL
SELECT 15 UNION ALL
SELECT 0 UNION ALL
SELECT 78--SQL查询如下:SELECT AVG(data*1.),SUM(data)*1./COUNT(*),SUM(data)
FROM @T/*
--------------------------------------- --------------------------------------- -----------
43.800000 43.80000000000 219(1 行受影响)*/
insert into @t select 92 union all
select 34 union all
select 15 union all
select null union all
select 78select avg(num) from @t/*
-----------
54
*/
drop table tb
go
create table tb([col] int)
insert tb select 92
insert tb select 34
insert tb select 15
insert tb select 0
insert tb select 78
go
select sum(col)/sum(case col when 0 then 0 else 1 end) colavg from tb
/*
colavg
-----------
54(1 行受影响)
*/
select avg(col) colavg from tb
/*
colavg
-----------
43(1 行受影响)
*/
go
create table [tb]([val] int)
insert [tb]
select 92 union all
select 34 union all
select 15 union all
select 0 union all
select 78
goselect avg(val) from [tb]
/*
43
*/
select @@version
/*
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
*/
2005 sp3下也是正常的。
一般avg会忽略null值,但不会忽略0值。
avg(val)等价于sum(val)/count(val),但不等价于sum(val)/count(*)。
在做统计时,可能需要用到后者。
测试数据:
declare @T table(id int,num float)
insert into @T
select 1,92 union all
select 2,34 union all
select 3,15 union all
select 4,0 union all
select 5,78查询:
select avg(num) as num
from @T