表A
字段
ID CL Num1 Num2
1 1 1
2 1 1
3 2 2
4 2 2
5 3 3 3
6 3 3 3
条件
如果 Num1=""的记录,Num2*CL
Num2=""的记录,Num1*CL
Num1<>"" 和 Num1<>""的记录,Num1*CL
总和/CL总和
也就是:((1*1+1*1)+(2*2+2*2)+(3*3+3*3))/(1+1+2+2+3+3)
要实现这样的功能sql语句怎么写?谢谢
字段
ID CL Num1 Num2
1 1 1
2 1 1
3 2 2
4 2 2
5 3 3 3
6 3 3 3
条件
如果 Num1=""的记录,Num2*CL
Num2=""的记录,Num1*CL
Num1<>"" 和 Num1<>""的记录,Num1*CL
总和/CL总和
也就是:((1*1+1*1)+(2*2+2*2)+(3*3+3*3))/(1+1+2+2+3+3)
要实现这样的功能sql语句怎么写?谢谢
(ID Int,
CL Int,
Num1 Int,
Num2 Int)
Insert A Select 1, 1, Null, 1
Union All Select 2, 1, Null, 1
Union All Select 3, 2, 2, Null
Union All Select 4, 2, 2, Null
Union All Select 5, 3, 3, 3
Union All Select 6, 3, 3, 3
GO
Select SUM(IsNull(Num1,Num2) *CL)*1.0/SUM(CL) From A
GO
Drop Table A
--Result
/*
2.333333333333
*/
insert a select 1,1,null,1
union all select 2,1,null,1
union all select 3,2,2,null
union all select 4,2,2,null
union all select 5,3,3,3
union all select 6,3,3,3select sum(cl*(case when num1 is null then num2 else num1 end))*1.0/sum(cl) from a
Num2=0的记录,Num1*CL
Num1<>0 和 Num1<>0的记录,Num1*CL
总和/CL总和
如果 Num1=0的记录,Num2*CL
Num2=0的记录,Num1*CL
Num1<>0 和 Num1<>0的记录,Num1*CL
总和/CL总和
----------------------------------------------------------Create Table A
(ID Int,
CL Int,
Num1 Int,
Num2 Int)
Insert A Select 1, 1, 0, 1
Union All Select 2, 1, 0, 1
Union All Select 3, 2, 2, 0
Union All Select 4, 2, 2, 0
Union All Select 5, 3, 3, 3
Union All Select 6, 3, 3, 3
GO
Select SUM((Case When Num1<>0 Then Num1 Else Num2 End) *CL)*1.0/SUM(CL) From A
GO
Drop Table A
--Result
/*
2.333333333333
*/