有这样一个表 A B C ============================= 5 4 <null> 4 3 3 5 <null> <null> 想通过SQL语句 直接出来下面的表 A B C D ======================================= 5 4 <null> 4.6 (是A×60% + B×40%) 4 3 3 3.5 (是A×50% + B×30% + C×20%) 5 <null> <null> 5 (是A×100%) 也就是说 判断C是null 则 根据A×60% + B×40% 得出D 如果不是 null 则根据A×50% + B×30% + C×20%得出D 如果ABC中只有一个数据 剩下两个为null则 D就是该数据的100%select a,b,c,d=case when c is null then a*0.6+b*0.4 else a*0.5+b*0.3+c*0.2 end from table
create table #test ( a int, b int, c int, )insert into #test select 5,4,null union all select 5,3,3 union all select 5,null,nullselect * from #testselect a,b,c, d= (case when (c is null and a is not null and b is not null )then a*0.6 + b*0.4 else case when (c is not null and b is not null and c is not null) then a*0.5+b*0.3+c*0.2 else case when (a is not null and b is null and c is null) then a else case when (a is null and b is not null and c is null) then b else case when (a is null and b is null and c is not null) then c end end end end end) from #testa b c d ----------- ----------- ----------- --------------------------------------- 5 4 NULL 4.6 5 3 3 4.0 5 NULL NULL 5.0(3 row(s) affected)
select *, D=(case when A is not null and B is not null and C is not null then A*0.5+B*0.3+C*0.2 when A is not null and B is not null and C is null then A*0.6+B*0.4 when A is not null and B is null and C is null then A when A is null and B is not null and C is null then B when A is null and B is null and C is not null then C ELSE NULL END) from tb
select *, D=(case when A is not null and B is not null and C is not null then A*0.5+B*0.3+C*0.2 when A is not null and B is not null and C is null then A*0.6+B*0.4 when A is not null and B is null and C is null then A when A is null and B is not null and C is null then B when A is null and B is null and C is not null then C ELSE NULL END) from tb
insert 新表 select A,B,C, case when (A is not null and B is not null and C is not null) then A*0.5 + B*0.3 + C*0.2 else case when (A is not null and B is not null) then A*0.6 + B*0.4 else case when A is not null then A else case when B is not null then B else case when C is not null then C else null end end end end end from 原表
A B C
=============================
5 4 <null>
4 3 3
5 <null> <null> 想通过SQL语句 直接出来下面的表
A B C D
=======================================
5 4 <null> 4.6 (是A×60% + B×40%)
4 3 3 3.5 (是A×50% + B×30% + C×20%)
5 <null> <null> 5 (是A×100%) 也就是说 判断C是null 则 根据A×60% + B×40% 得出D
如果不是 null 则根据A×50% + B×30% + C×20%得出D
如果ABC中只有一个数据 剩下两个为null则 D就是该数据的100%select a,b,c,d=case when c is null then a*0.6+b*0.4 else
a*0.5+b*0.3+c*0.2 end from table
create table #test
(
a int,
b int,
c int,
)insert into #test
select 5,4,null
union all
select 5,3,3
union all
select 5,null,nullselect * from #testselect a,b,c,
d= (case when (c is null and a is not null and b is not null )then a*0.6 + b*0.4
else case when (c is not null and b is not null and c is not null) then a*0.5+b*0.3+c*0.2
else case when (a is not null and b is null and c is null) then a
else case when (a is null and b is not null and c is null) then b
else case when (a is null and b is null and c is not null) then c
end
end
end
end
end)
from #testa b c d
----------- ----------- ----------- ---------------------------------------
5 4 NULL 4.6
5 3 3 4.0
5 NULL NULL 5.0(3 row(s) affected)
D=(case when A is not null and B is not null and C is not null then A*0.5+B*0.3+C*0.2
when A is not null and B is not null and C is null then A*0.6+B*0.4
when A is not null and B is null and C is null then A
when A is null and B is not null and C is null then B
when A is null and B is null and C is not null then C
ELSE NULL END)
from tb
D=(case when A is not null and B is not null and C is not null then A*0.5+B*0.3+C*0.2
when A is not null and B is not null and C is null then A*0.6+B*0.4
when A is not null and B is null and C is null then A
when A is null and B is not null and C is null then B
when A is null and B is null and C is not null then C
ELSE NULL END)
from tb
樓上的幾位兄弟都把語句寫出來了.
insert 新表
select A,B,C,
case when (A is not null and B is not null and C is not null) then A*0.5 + B*0.3 + C*0.2
else case when (A is not null and B is not null) then A*0.6 + B*0.4
else case when A is not null then A
else case when B is not null then B
else case when C is not null then C
else null
end
end
end
end
end
from 原表