22,207
社区成员
发帖
与我相关
我的任务
分享
select *,
ColAvg=(isnull(Col1,0)+isnull(Col2,0)+isnull(Col3,0)+isnull(Col4,0)+isnull(Col5,0))/
(5+isnull(Col1,-1)+isnull(Col2,-1)+isnull(Col3,-1)+isnull(Col4,-1)+isnull(Col5,-1)
-isnull(Col1,0)-isnull(Col2,0)-isnull(Col3,0)-isnull(Col4,0)-isnull(Col5,0))
from #
ID Col1 Col2 Col3 Col4 Col5 ColAvg
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 1 2 3 NULL NULL 2
2 2 2 1 3 8 3
(2 行受影响)
SELECT #.*,t.ColAvg FROM # join
(
SELECT ID,[ColAvg]=avg(Col)
FROM
(
SELECT id,Col1 Col FROM # union all
SELECT id,Col2 FROM # union all
SELECT id,Col3 FROM # union all
SELECT id,Col4 FROM # union all
SELECT id,Col5 FROM #
) T
GROUP BY ID
) t
ON #.ID=t.ID
ID Col1 Col2 Col3 Col4 Col5 ColAvg
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 1 2 3 NULL NULL 2
2 2 2 1 3 8 3
警告: 聚合或其他 SET 操作消除了 Null 值。
(2 行受影响)
CREATE TABLE #
(ID int identity,Col1 int,Col2 int,Col3 int,Col4 int,Col5 int)
INSERT INTO #
SELECT 1,2,3,null,null union all
SELECT 2,2,1,3,8
SELECT * FROM #
ID Col1 Col2 Col3 Col4 Col5
----------- ----------- ----------- ----------- ----------- -----------
1 1 2 3 NULL NULL
2 2 2 1 3 8
(2 行受影响)
SELECT ID,[ColAvg]=avg(Col)
FROM
(
SELECT id,Col1 Col FROM # union all
SELECT id,Col2 FROM # union all
SELECT id,Col3 FROM # union all
SELECT id,Col4 FROM # union all
SELECT id,Col5 FROM #
) T
GROUP BY ID
ID ColAvg
----------- -----------
1 2
2 3
SELECT (ISNULL(字段1,0)+ISNULL(字段2,0)+ISNULL(字段3,0)+ISNULL(字段4,0)+ISNULL(字段5,0))/5.0
FROM TB
use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
字段1 int,
字段2 int,
字段3 int,
字段4 int,
字段5 int
)
go
--插入测试数据
insert into tb select 1,2,3,NUll,NULL
union all select 1,3,0,7,NULL
go
--代码实现
select *,平均值=cast((isnull(字段1,0)+isnull(字段2,0)+isnull(字段3,0)+isnull(字段4,0)+isnull(字段5,0))*1.0/
((case when 字段1 is null then 0 else 1 end)+
(case when 字段2 is null then 0 else 1 end)+
(case when 字段3 is null then 0 else 1 end)+
(case when 字段4 is null then 0 else 1 end)+
(case when 字段5 is null then 0 else 1 end)) as decimal(10,2))
from tb
/*测试结果
字段1 字段2 字段3 字段4 字段5 平均值
---------------------------------------------------
1 2 3 NULL NULL 2.00
1 3 0 7 NULL 2.75
(2 行受影响)
*/
--> 测试数据:#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(a int, b int, c int, d varchar(8), e varchar(8))
insert into #
select 1, 2, 3, null, null
select [avg] = (isnull(a,0) + isnull(b,0) + isnull(c,0) + isnull(d,0) +isnull(e,0)) / (isnull(abs(sign(a)),0) + isnull(abs(sign(b)),0) + isnull(abs(sign(c)),0) + isnull(abs(sign(d)),0) + isnull(abs(sign(e)),0)) from #
/*
avg
----------------------
2
*/
看下4楼的是否满足你的要求
if object_id('tb') is not null drop table tb
go
create table tb
(
字段1 int,
字段2 int,
字段3 int,
字段4 int,
字段5 int
)
insert into tb select 1,2,3,null,null
go
select sum(isnull(字段1,0)+isnull(字段2,0)+isnull(字段3,0)+isnull(字段4,0)+isnull(字段5,0))/
sum((case when 字段1>0 then 1 else 0 end)+
(case when 字段2>0 then 1 else 0 end)+
(case when 字段3>0 then 1 else 0 end)+
(case when 字段4>0 then 1 else 0 end)+
(case when 字段5>0 then 1 else 0 end)) '平均值'
from tb
平均值
-----------
2
(1 行受影响)