那你需要处理成什么值才合适?还是不处理这条记录?如果不处理,着在查询时筛选掉.select * from tb where col is not null
use test go if object_id('test.dbo.tb') is not null drop table tb -- 创建数据表 create table tb ( num int ) go --插入测试数据 insert into tb select 3 union all select 2 union all select null union all select 1 go --代码实现select num_avg=avg(cast(num as float)) from tb --不将null作为记录统计/*测试结果num_avg ------------------ 2(3 行受影响) */select num_avg=avg(cast(isnull(num,0) as float)) from tb --将null作为记录统计 /*测试结果num_avg ------------------ 1.5(3 行受影响) */
数据库处理不就完了吗 select ID, avg(hole1) from ( select ID,hole1 from tb union all select ID,hole2 from tb union all select ID,hole3 from tb union all select ID,hole4 from tb union all select ID,hole5 from tb union all ……………… select ID,hole30 from tb ) as t group by ID
呵呵,很久没看恐怖片子了,胆小。正题:数据库计算avg-- 测试数据:# if object_id('tempdb.dbo.#') is not null drop table # create table #(id int, hole1 float, hole2 float, hole3 float) insert into # select 1001, null, 9, 2 union all select 1002, 3, null, 7 union all select 1003, 8, 1, nullselect *, [avg] = ( select avg(hole1) from ( select hole1 union all select hole2 union all select hole3 ) t ) from #/* id hole1 hole2 hole3 avg ----------- ---------------------- ---------------------- ---------------------- ---------------------- 1001 NULL 9 2 5.5 1002 3 NULL 7 5 1003 8 1 NULL 4.5 */
要么列转行,只有id,hole两行,,select id,count(distinct hole) from tb group by id统计个数;sum(case when hole1 is null then 0 else 1 end +case when hole2 is null then 0 else 1 end......)
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
num int
)
go
--插入测试数据
insert into tb select 3
union all select 2
union all select null
union all select 1
go
--代码实现select num_avg=avg(cast(num as float)) from tb --不将null作为记录统计/*测试结果num_avg
------------------
2(3 行受影响)
*/select num_avg=avg(cast(isnull(num,0) as float)) from tb --将null作为记录统计
/*测试结果num_avg
------------------
1.5(3 行受影响)
*/
我用的是一条记录内部的数据,大概30个字段,通过查询每个字段赋值到数组中,假如遇到空的我现在是处理成0,但这样得出的数值时不争取的,比如有6个字段为null,这些0参与计算式不正确的,本来只要除以24,现在是除以30,困扰
where 字段 is not null
ID hole1 hole2 hole3 hole4 hole5 ……………… hole30
1001 11.2 12.4 null 14.6 17.8 12.6我需要统计的是hole1 到hole 30的平均值,其中有几个字段是null,如何处理?
select ID, avg(hole1) from
(
select ID,hole1 from tb union all
select ID,hole2 from tb union all
select ID,hole3 from tb union all
select ID,hole4 from tb union all
select ID,hole5 from tb union all
………………
select ID,hole30 from tb
) as t
group by ID
if object_id('tempdb.dbo.#') is not null drop table #
create table #(id int, hole1 float, hole2 float, hole3 float)
insert into #
select 1001, null, 9, 2 union all
select 1002, 3, null, 7 union all
select 1003, 8, 1, nullselect *, [avg] =
(
select avg(hole1) from
(
select hole1 union all
select hole2 union all
select hole3
) t
)
from #/*
id hole1 hole2 hole3 avg
----------- ---------------------- ---------------------- ---------------------- ----------------------
1001 NULL 9 2 5.5
1002 3 NULL 7 5
1003 8 1 NULL 4.5
*/
要么列转行,只有id,hole两行,,select id,count(distinct hole) from tb group by id统计个数;sum(case when hole1 is null then 0 else 1 end +case when hole2 is null then 0 else 1 end......)
你的要求就是算法,有了你的算法才可能用SQL语句实现。
你的要求就是算法,有了你的算法才可能用SQL语句实现。
**********************
还要补充一句话:null值的含义是空缺;4,null,8三个数值的平均值也应该为空缺 null。