我的数据表如下
字段1 字段2 字段3 字段4 字段5 字段6 字段7
1 3 5 6 6 30 33
4 5 8 9 12 34 35
我想要的结果是再增加一个字段8,把每条记录中的数字判断其奇偶性,并把奇数:偶数的比例存放到字段8中,
比如,
在记录1中,有 1,6,16,30 4个偶数,有3,5,33 3个奇数,则字段8的值是, 4:3
网友Woy_88兄的方法:
declare
@t
table
(字段1
int
,字段2
int
, 字段3
int
, 字段4
int
, 字段5
int
, 字段6
int
,字段7
int
)
insert @t select 1 , 3 , 5 , 6 , 16 , 30 , 33
insert @t select 4 , 5 , 8 , 9 , 12 , 34 , 35
select
* ,
[ 字段8 ] = ( select rtrim ( sum ( case when col % 2 = 1 then 1 else 0 end )) + ' : ' + rtrim ( sum ( case when col % 2 = 0 then 1 else 0 end ))
from ( select 字段1 as col union all select 字段2 union all select 字段3 union all select 字段4
union all select 字段5 union all select 字段6 union all select 字段7)t )
from
@t
(所影响的行数为 1 行)
(所影响的行数为 1 行)
字段1 字段2 字段3 字段4 字段5 字段6 字段7 字段8
-- --------- ----------- ----------- ----------- ----------- ----------- ----------- -----
1 3 5 6 16 30 33 4 : 3
4 5 8 9 12 34 35 3 : 4
(所影响的行数为 2 行)
insert @t select 1 , 3 , 5 , 6 , 16 , 30 , 33
insert @t select 4 , 5 , 8 , 9 , 12 , 34 , 35
select
* ,
[ 字段8 ] = ( select rtrim ( sum ( case when col % 2 = 1 then 1 else 0 end )) + ' : ' + rtrim ( sum ( case when col % 2 = 0 then 1 else 0 end ))
from ( select 字段1 as col union all select 字段2 union all select 字段3 union all select 字段4
union all select 字段5 union all select 字段6 union all select 字段7)t )
from
@t
(所影响的行数为 1 行)
(所影响的行数为 1 行)
字段1 字段2 字段3 字段4 字段5 字段6 字段7 字段8
-- --------- ----------- ----------- ----------- ----------- ----------- ----------- -----
1 3 5 6 16 30 33 4 : 3
4 5 8 9 12 34 35 3 : 4
(所影响的行数为 2 行)
playwarcraft 兄的解决方法:
declare
@t
table
(a
int
,b
int
, c
int
, d
int
, e
int
, f
int
,g
int
)
insert @t select 1 , 3 , 5 , 6 , 16 , 30 , 33
insert @t select 4 , 5 , 8 , 9 , 12 , 34 , 35
select * ,
h = rtrim (
( case when a % 2 = 0 then 1 else 0 end )
+ ( case when b % 2 = 0 then 1 else 0 end )
+ ( case when c % 2 = 0 then 1 else 0 end )
+ ( case when d % 2 = 0 then 1 else 0 end )
+ ( case when e % 2 = 0 then 1 else 0 end )
+ ( case when f % 2 = 0 then 1 else 0 end )
+ ( case when g % 2 = 0 then 1 else 0 end )
) + ' : ' +
rtrim (
( case when a % 2 = 1 then 1 else 0 end )
+ ( case when b % 2 = 1 then 1 else 0 end )
+ ( case when c % 2 = 1 then 1 else 0 end )
+ ( case when d % 2 = 1 then 1 else 0 end )
+ ( case when e % 2 = 1 then 1 else 0 end )
+ ( case when f % 2 = 1 then 1 else 0 end )
+ ( case when g % 2 = 1 then 1 else 0 end )
)
from @t
/*
--------------------------------------------------------------
1 3 5 6 16 30 33 3:4
4 5 8 9 12 34 35 4:3
*/
insert @t select 1 , 3 , 5 , 6 , 16 , 30 , 33
insert @t select 4 , 5 , 8 , 9 , 12 , 34 , 35
select * ,
h = rtrim (
( case when a % 2 = 0 then 1 else 0 end )
+ ( case when b % 2 = 0 then 1 else 0 end )
+ ( case when c % 2 = 0 then 1 else 0 end )
+ ( case when d % 2 = 0 then 1 else 0 end )
+ ( case when e % 2 = 0 then 1 else 0 end )
+ ( case when f % 2 = 0 then 1 else 0 end )
+ ( case when g % 2 = 0 then 1 else 0 end )
) + ' : ' +
rtrim (
( case when a % 2 = 1 then 1 else 0 end )
+ ( case when b % 2 = 1 then 1 else 0 end )
+ ( case when c % 2 = 1 then 1 else 0 end )
+ ( case when d % 2 = 1 then 1 else 0 end )
+ ( case when e % 2 = 1 then 1 else 0 end )
+ ( case when f % 2 = 1 then 1 else 0 end )
+ ( case when g % 2 = 1 then 1 else 0 end )
)
from @t
/*
--------------------------------------------------------------
1 3 5 6 16 30 33 3:4
4 5 8 9 12 34 35 4:3
*/
--
似乎看上去干凈一點。。。
declare @t table (a int ,b int , c int , d int , e int , f int ,g int )
insert @t select 1 , 3 , 5 , 6 , 16 , 30 , 33
insert @t select 4 , 5 , 8 , 9 , 12 , 34 , 35
select a,b,c,d,e,f,g,
h = rtrim (h) + ' : ' + rtrim ( 7 - h)
from
(
select * ,(a % 2 ) + (b % 2 ) + (c % 2 ) + (d % 2 ) + (e % 2 ) + (f % 2 ) + (g % 2 ) as h
from @t
) T
/*
1 3 5 6 16 30 33 4:3
4 5 8 9 12 34 35 3:4
*/
declare @t table (a int ,b int , c int , d int , e int , f int ,g int )
insert @t select 1 , 3 , 5 , 6 , 16 , 30 , 33
insert @t select 4 , 5 , 8 , 9 , 12 , 34 , 35
select a,b,c,d,e,f,g,
h = rtrim (h) + ' : ' + rtrim ( 7 - h)
from
(
select * ,(a % 2 ) + (b % 2 ) + (c % 2 ) + (d % 2 ) + (e % 2 ) + (f % 2 ) + (g % 2 ) as h
from @t
) T
/*
1 3 5 6 16 30 33 4:3
4 5 8 9 12 34 35 3:4
*/
我个人的解决方法:
借用playwarcraft 兄的语句,改一个字符和一数字,使用位运算方法如下:
declare
@t
table
(a
int
,b
int
, c
int
, d
int
, e
int
, f
int
,g
int
)
insert @t select 1 , 3 , 5 , 6 , 16 , 30 , 33
insert @t select 4 , 5 , 8 , 9 , 12 , 34 , 35
select * ,
h = rtrim (
( case when a & 1 = 0 then 1 else 0 end )
+ ( case when b & 1 = 0 then 1 else 0 end )
+ ( case when c & 1 = 0 then 1 else 0 end )
+ ( case when d & 1 = 0 then 1 else 0 end )
+ ( case when e & 1 = 0 then 1 else 0 end )
+ ( case when f & 1 = 0 then 1 else 0 end )
+ ( case when g & 1 = 0 then 1 else 0 end )
) + ' : ' +
rtrim (
( case when a & 1 = 1 then 1 else 0 end )
+ ( case when b & 1 = 1 then 1 else 0 end )
+ ( case when c & 1 = 1 then 1 else 0 end )
+ ( case when d & 1 = 1 then 1 else 0 end )
+ ( case when e & 1 = 1 then 1 else 0 end )
+ ( case when f & 1 = 1 then 1 else 0 end )
+ ( case when g & 1 = 1 then 1 else 0 end )
)
from @t
/*
结果:
---------------------------------------------------------------
1 3 5 6 16 30 33 3:4
4 5 8 9 12 34 35 4:3
*/
insert @t select 1 , 3 , 5 , 6 , 16 , 30 , 33
insert @t select 4 , 5 , 8 , 9 , 12 , 34 , 35
select * ,
h = rtrim (
( case when a & 1 = 0 then 1 else 0 end )
+ ( case when b & 1 = 0 then 1 else 0 end )
+ ( case when c & 1 = 0 then 1 else 0 end )
+ ( case when d & 1 = 0 then 1 else 0 end )
+ ( case when e & 1 = 0 then 1 else 0 end )
+ ( case when f & 1 = 0 then 1 else 0 end )
+ ( case when g & 1 = 0 then 1 else 0 end )
) + ' : ' +
rtrim (
( case when a & 1 = 1 then 1 else 0 end )
+ ( case when b & 1 = 1 then 1 else 0 end )
+ ( case when c & 1 = 1 then 1 else 0 end )
+ ( case when d & 1 = 1 then 1 else 0 end )
+ ( case when e & 1 = 1 then 1 else 0 end )
+ ( case when f & 1 = 1 then 1 else 0 end )
+ ( case when g & 1 = 1 then 1 else 0 end )
)
from @t
/*
结果:
---------------------------------------------------------------
1 3 5 6 16 30 33 3:4
4 5 8 9 12 34 35 4:3
*/
位运算的优化方法
declare
@t
table
(a
int
,b
int
, c
int
, d
int
, e
int
, f
int
,g
int
)
insert @t select 1 , 3 , 5 , 6 , 16 , 30 , 33
insert @t select 4 , 5 , 8 , 9 , 12 , 34 , 35
select * ,
h = ltrim ((( 1 & a) ^ 0 ) + (( 1 & b) ^ 0 ) + (( 1 & c) ^ 0 ) + (( 1 & d) ^ 0 ) + (( 1 & e) ^ 0 ) + (( 1 & f) ^ 0 ) + (( 1 & g) ^ 0 )) + ' : ' +
ltrim ((( 1 & a) ^ 1 ) + (( 1 & b) ^ 1 ) + (( 1 & c) ^ 1 ) + (( 1 & d) ^ 1 ) + (( 1 & e) ^ 1 ) + (( 1 & f) ^ 1 ) + (( 1 & g) ^ 1 ))
from @t
/*
结果:
---------------------------------------------------------------
1 3 5 6 16 30 33 3:4
4 5 8 9 12 34 35 4:3
*/
insert @t select 1 , 3 , 5 , 6 , 16 , 30 , 33
insert @t select 4 , 5 , 8 , 9 , 12 , 34 , 35
select * ,
h = ltrim ((( 1 & a) ^ 0 ) + (( 1 & b) ^ 0 ) + (( 1 & c) ^ 0 ) + (( 1 & d) ^ 0 ) + (( 1 & e) ^ 0 ) + (( 1 & f) ^ 0 ) + (( 1 & g) ^ 0 )) + ' : ' +
ltrim ((( 1 & a) ^ 1 ) + (( 1 & b) ^ 1 ) + (( 1 & c) ^ 1 ) + (( 1 & d) ^ 1 ) + (( 1 & e) ^ 1 ) + (( 1 & f) ^ 1 ) + (( 1 & g) ^ 1 ))
from @t
/*
结果:
---------------------------------------------------------------
1 3 5 6 16 30 33 3:4
4 5 8 9 12 34 35 4:3
*/
问题来源:topic.csdn.net/u/20071031/13/b0c0fe45-7016-41b0-99cc-46075fd0339d.html