27,579
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-11-26 10:57:24
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:#tab1
if object_id('tempdb.dbo.#tab1') is not null drop table #tab1
go
create table #tab1([code] varchar(2),[js1] int,[js2] int,[js3] int)
insert #tab1
select 'A1',10,12,14 union all
select 'A2',31,32,30 union all
select 'A3',26,28,null
--------------开始查询--------------------------
select b.*,a.ave
from
(
select code,sum(js)/sum(case when js>0 then 1 else 0 end) as ave
from
(
select code,isnull(js1,0) as js from #tab1
union all
select code,isnull(js2,0) as js from #tab1
union all
select code,isnull(js3,0) as js from #tab1
) as t
group by
code) as a inner join #tab1 as b on a.code=b.code
----------------结果----------------------------
/* code js1 js2 js3 ave
---- ----------- ----------- ----------- -----------
A1 10 12 14 12
A2 31 32 30 31
A3 26 28 NULL 27
(3 行受影响)
*/
SELECT code,js1,js2,js3
,(ISNULL(js1,0)+ISNULL(js2,0)+ISNULL(js3,0))/
CASE WHEN js1 IS NULL AND js2 IS NULL AND js3 IS NULL THEN 1
ELSE (ISNULL(POWER(js1,0),0)+ISNULL(POWER(js2,0),0)+ISNULL(POWER(js3,0),0)) END ave
FROM TB
你试下SELECT code,js1,js2,js3
,(ISNULL(js1,0)+ISNULL(js2,0)+ISNULL(js3,0))/
CASE WHEN(ISNULL(POWER(js1,0),0)+ISNULL(POWER(js2,0),0)+ISNULL(POWER(js3,0),0))=0 THEN 1 END ave
FROM TB
select *,avg(isnull(js1,0)+isnull(js2,0)+isnull(js3,0)) from tb