--如果要更新表 update a set 积分=(case when birthday is not null then 2 else 0 end) +(case when content is not null then 5 else 0 end) --如果只是查询: select id,积分=(case when birthday is not null then 2 else 0 end) +(case when content is not null then 5 else 0 end) from a
select id, 2*((case when name is not null then 1 else 0 end) +(case when sex is not null then 1 else 0 end) +(case when birthday is not null then 1 else 0 end)) +5*(case when content is not null then 1 else 0 end) jifen from a
select id, [积分]=case when content is not null then 5 else 0 end + case when name is not null and sex is not null and birthday is not null then 2 else 0 end from a
case when name is not null then 2 else 0 end + case when content is not null then 5 else 0 end
declare @t table (id int,name varchar(8),sex varchar(3), birthday datetime,[content] varchar(2)) insert into @t select 1,'zhangsan','nan','2010-10-10','hh' union all select 2,'lisi','nv',null,null union all select 3,'wangwu','nan','2010-10-01',null union all select 4,null,null,'2010-01-01','hh' union all select 5,null,null,null,nullselect id, 总积分=ISNULL(REPLACE(name,name,'2'),0)*1+ISNULL(REPLACE(sex,sex,'2'),0) +ISNULL(REPLACE(birthday,birthday,'2'),0)+ISNULL(REPLACE([content],[content],'5'),0) from @t /* id 总积分 ----------- ----------- 1 11 2 4 3 6 4 7 5 0 */
update a
set 积分=(case when birthday is not null then 2 else 0 end)
+(case when content is not null then 5 else 0 end)
--如果只是查询:
select id,积分=(case when birthday is not null then 2 else 0 end)
+(case when content is not null then 5 else 0 end)
from a
select id,
2*((case when name is not null then 1 else 0 end)
+(case when sex is not null then 1 else 0 end)
+(case when birthday is not null then 1 else 0 end))
+5*(case when content is not null then 1 else 0 end) jifen
from a
id,
[积分]=case when content is not null then 5 else 0 end
+ case when name is not null and sex is not null and birthday is not null then 2 else 0 end
from a
+
case when content is not null then 5 else 0 end
declare @t table
(id int,name varchar(8),sex varchar(3),
birthday datetime,[content] varchar(2))
insert into @t
select 1,'zhangsan','nan','2010-10-10','hh' union all
select 2,'lisi','nv',null,null union all
select 3,'wangwu','nan','2010-10-01',null union all
select 4,null,null,'2010-01-01','hh' union all
select 5,null,null,null,nullselect id,
总积分=ISNULL(REPLACE(name,name,'2'),0)*1+ISNULL(REPLACE(sex,sex,'2'),0)
+ISNULL(REPLACE(birthday,birthday,'2'),0)+ISNULL(REPLACE([content],[content],'5'),0)
from @t
/*
id 总积分
----------- -----------
1 11
2 4
3 6
4 7
5 0
*/