日期:2014-05-17 浏览次数:20776 次
select t.*, case when a1 = 1 and b1 != 1 then '不正确' else '正确' end b1, case when a2 = 1 and b2 != 1 then '不正确' else '正确' end b2, case when a3 = 1 and b3 != 1 then '不正确' else '正确' end b3, from t
------解决方案--------------------
[code=SQL0]--这是SQL中写法,oracle中一样.
create table tb(年月 varchar(10) , a1 int,a2 int,a3 int,b1 int,b2 int,b3 int)
insert into tb values('0901', 0 , 1 , 1 , 0 , 1 , 1 )
insert into tb values('0902', 1 , 1 , 1 , 1 , 1 , 0 )
insert into tb values('0903', 1 , 1 , 1 , 1 , 1 , 0 )
go
select 年月,
case when a1 = 1 and b1 = 1 then '正确' else '不正确' end b1,
case when a2 = 1 and b2 = 1 then '正确' else '不正确' end b2,
case when a3 = 1 and b3 = 1 then '正确' else '不正确' end b3
from tb
drop table tb
/*
年月 b1 b2 b3
---------- ------ ------ ------
0901 不正确 正确 正确
0902 正确 正确 不正确
0903 正确 正确 不正确
(所影响的行数为 3 行)
*/[/code]
------解决方案--------------------
-- 客官您是要SELECT还是UPDATE呢? SQL> SELECT TT.*, 2 DECODE(ABS(SIGN(B1 - A1)) + ABS(SIGN(B2 - A2)) + ABS(SIGN(B3 - A3)), 3 0, 4 'CORRECT', 5 'WRONG') NEW_CODE 6 FROM TABLE_NAME TT; YEARS A1 A2 A3 B1 B2 B3 NEW_CODE ----- ---------- ---------- ---------- ---------- ---------- ---------- -------- 0901 0 1 1 0 1 1 CORRECT 0902 1 1 1 1 1 0 WRONG 0903 1 1 1 1 1 0 WRONG