select *, [相加1]=case when 数1=1 then 1 else 0 end+case when 数2=1 then 1 else 0 end +case when 数3=1 then 1 else 0 end+case when 数4=1 then 1 else 0 end, [相加2]=case when 数1=2 then 2 else 0 end+case when 数2=2 then 2 else 0 end +case when 数3=2 then 2 else 0 end+case when 数4=2 then 2 else 0 end from 表1
declare @表1 table (id int,数1 int,数2 int,数3 int,数4 int) insert into @表1 select 1,1,1,2,1 union all select 2,2,1,2,2 union all select 3,2,2,2,2select *,相加1=( case when 数1=1 then 1 else 0 end+case when 数2=1 then 1 else 0 end+ case when 数3=1 then 1 else 0 end+case when 数4=1 then 1 else 0 end), 相加2=2*( case when 数1=2 then 1 else 0 end+case when 数2=2 then 1 else 0 end+ case when 数3=2 then 1 else 0 end+case when 数4=2 then 1 else 0 end) from @表1 /* id 数1 数2 数3 数4 相加1 相加2 ----------- ----------- ----------- ----------- ----------- ----------- ----------- 1 1 1 2 1 3 2 2 2 1 2 2 1 6 3 2 2 2 2 0 8 */
declare @表1 table (id int,数1 int,数2 int,数3 int,数4 int) insert into @表1 select 1,1,1,2,1 union all select 2,2,1,2,2 union all select 3,2,2,2,2--纯娱乐写法,效率太低 select *, 相加1=(len(',,'+ltrim(数1)+',,,'+ltrim(数2)+ ',,,'+ltrim(数3)+',,,'+ltrim(数4)+',,')- len(replace(',,'+ltrim(数1)+',,,'+ltrim(数2)+ ',,,'+ltrim(数3)+',,,'+ltrim(数4)+',,',',1,','')))/3, 相加2=(len(',,'+ltrim(数1)+',,,'+ltrim(数2)+ ',,,'+ltrim(数3)+',,,'+ltrim(数4)+',,')- len(replace(',,'+ltrim(数1)+',,,'+ltrim(数2)+ ',,,'+ltrim(数3)+',,,'+ltrim(数4)+',,',',2,','')))/3*2 from @表1 /* id 数1 数2 数3 数4 相加1 相加2 ----------- ----------- ----------- ----------- ----------- ----------- ----------- 1 1 1 2 1 3 2 2 2 1 2 2 1 6 3 2 2 2 2 0 8 */
--哈哈,这样岂不是看着更清爽 select *, 相加1=((数1%2)+(数2%2)+(数3%2)+(数4%2))*2 相加2=((数1/2)+(数2/2)+(数3/2)+(数4/2))*2 from @表1
--前面似乎看到树哥这样写 [相加2]=case when 数1=2 then 1 else 0 end+case when 数2=2 then 1 else 0 end +case when 数3=2 then 1 else 0 end+case when 数4=2 then 1 else 0 end所以把树哥的复制下来,红色部分改为2再发出去,但发完再看树哥写的怎么是2了,原来不是1吗? 以为树哥是在写2时还是复制上面那个等于1的一直没改过来,但 肯定是自己眼花了,呵呵
[相加1]=case when 数1=1 then 1 else 0 end+case when 数2=1 then 1 else 0 end
+case when 数3=1 then 1 else 0 end+case when 数4=1 then 1 else 0 end,
[相加2]=case when 数1=2 then 2 else 0 end+case when 数2=2 then 2 else 0 end
+case when 数3=2 then 2 else 0 end+case when 数4=2 then 2 else 0 end
from 表1
declare @表1 table (id int,数1 int,数2 int,数3 int,数4 int)
insert into @表1
select 1,1,1,2,1 union all
select 2,2,1,2,2 union all
select 3,2,2,2,2select *,相加1=(
case when 数1=1 then 1 else 0 end+case when 数2=1 then 1 else 0 end+
case when 数3=1 then 1 else 0 end+case when 数4=1 then 1 else 0 end),
相加2=2*(
case when 数1=2 then 1 else 0 end+case when 数2=2 then 1 else 0 end+
case when 数3=2 then 1 else 0 end+case when 数4=2 then 1 else 0 end)
from @表1
/*
id 数1 数2 数3 数4 相加1 相加2
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 1 1 2 1 3 2
2 2 1 2 2 1 6
3 2 2 2 2 0 8
*/
declare @表1 table (id int,数1 int,数2 int,数3 int,数4 int)
insert into @表1
select 1,1,1,2,1 union all
select 2,2,1,2,2 union all
select 3,2,2,2,2--纯娱乐写法,效率太低
select *,
相加1=(len(',,'+ltrim(数1)+',,,'+ltrim(数2)+
',,,'+ltrim(数3)+',,,'+ltrim(数4)+',,')-
len(replace(',,'+ltrim(数1)+',,,'+ltrim(数2)+
',,,'+ltrim(数3)+',,,'+ltrim(数4)+',,',',1,','')))/3,
相加2=(len(',,'+ltrim(数1)+',,,'+ltrim(数2)+
',,,'+ltrim(数3)+',,,'+ltrim(数4)+',,')-
len(replace(',,'+ltrim(数1)+',,,'+ltrim(数2)+
',,,'+ltrim(数3)+',,,'+ltrim(数4)+',,',',2,','')))/3*2
from @表1
/*
id 数1 数2 数3 数4 相加1 相加2
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 1 1 2 1 3 2
2 2 1 2 2 1 6
3 2 2 2 2 0 8
*/
--哈哈,这样岂不是看着更清爽
select *,
相加1=((数1%2)+(数2%2)+(数3%2)+(数4%2))*2
相加2=((数1/2)+(数2/2)+(数3/2)+(数4/2))*2
from @表1
[相加2]=case when 数1=2 then 1 else 0 end+case when 数2=2 then 1 else 0 end
+case when 数3=2 then 1 else 0 end+case when 数4=2 then 1 else 0 end所以把树哥的复制下来,红色部分改为2再发出去,但发完再看树哥写的怎么是2了,原来不是1吗?
以为树哥是在写2时还是复制上面那个等于1的一直没改过来,但
肯定是自己眼花了,呵呵