inf2232_pay1 , inf2234_pay3 , inf2235_pay4 code
10 20 30 001
100 60 80 002
20 90 10 003
将inf2232_pay1 , inf2234_pay3 , inf2235_pay4 比较后得出其最大值，如下结果
inf2232_pay1 , inf2234_pay3 , inf2235_pay4 code
0 0 30 001
100 0 0 002
0 90 0 003
------解决方案--------------------
case when
------解决方案--------------------
create table af
(inf2232_pay1 int,inf2234_pay3 int,inf2235_pay4 int,code varchar(10))
insert into af
select 10,20,30,'001' union all
select 100,60,80,'002' union all
select 20,90,10,'003'
update af
set inf2232_pay1=case when inf2232_pay1>inf2234_pay3 and inf2232_pay1>inf2235_pay4
then inf2232_pay1 else 0 end,
inf2234_pay3=case when inf2234_pay3>inf2232_pay1 and inf2234_pay3>inf2235_pay4
then inf2234_pay3 else 0 end,
inf2235_pay4=case when inf2235_pay4>inf2232_pay1 and inf2235_pay4>inf2234_pay3
then inf2235_pay4 else 0 end
select * from af
/*
inf2232_pay1 inf2234_pay3 inf2235_pay4 code
------------ ------------ ------------ ----------
0 0 30 001
100 0 0 002
0 90 0 003
(3 row(s) affected)
*/
------解决方案--------------------
是这样吗：
;with t(inf2232_pay1 , inf2234_pay3 , inf2235_pay4 , code)
as
(
select 10 , 20, 30, '001'
union all select 100, 60, 80, '002'
union all select 20, 90, 10, '003'
)
select case when inf2232_pay1> inf2234_pay3
and inf2232_pay1> inf2235_pay4
then inf2232_pay1
else 0 end as inf2232_pay1,
case when inf2234_pay3> inf2232_pay1
and inf2234_pay3> inf2235_pay4