如何写这样一条SQL语句?????(在线等待)

[zgtt]

表结构

表名 A

列名 类型
f1 nvarchar(10)
f2 nvarchar(10)
f3 nvarchar(10)
f4 nvarchar(10)

f1,f2,f3中存的nvarchar类型的数字,取每行数据f1,f2,f3中的最大值填如f4中,但是f1,f2,f3中的数据因为有输入错误,致使有些值不能转换为数字,直接转换时提示异常,现在规定如果不能转换为数字的就认为是0,该如何写一个更新SQL语句来实现?谢谢

例如:
表中数据
f1 f2 f3 f4
null 111 324 null
2.2.2 3 8 null

执行UPDATE语句后为:
f1 f2 f3 f4
null 111 324 324
2.2.2 10 8 10

[zlp321002]

写个函数，更新吧！

[zgtt]

能用一条SQL语句实现吗?
是不是难度挺大?

[ReViSion]

update #1 set f1=case ISNUMERIC(f1) when 0 then null else f1 end,
f2=case ISNUMERIC(f2) when 0 then null else f2 end,
f3=case ISNUMERIC(f3) when 0 then null else f3 end,
f4=case when nullif(f1,0)>=nullif(f2,0) --f1>f2
then (case when nullif(f1,0)<nullif(f3,0) then f3
else f1
end)
else --f1<f2
(case when nullif(f2,0)<nullif(f3,0) then f3
else f2
end)
end

[$扫地僧$]

try:
create table A
(
f1 nvarchar(10),
f2 nvarchar(10),
f3 nvarchar(10),
f4 nvarchar(10)
)
insert A
select NUll,'345','334',NUll
select NUll,'111','324',NUll union
select '2.2.2','3','8',NUll

update A set F4=M.F4 from (select case when ISNUMERIC(isnull(f1,0))=0 then 0 else isnull(f1,0) end as f1,
case when ISNUMERIC(isnull(f2,0))=0 then 0 else isnull(f2,0) end as f2,
case when ISNUMERIC(isnull(f3,0))=0 then 0 else isnull(f3,0) end as f3
from A) T,
(select F1,F2,F3,
case when F1<=F2 and F2<=F3 then F3
when F1>=F2 and F2>=F3 then F1
when F2>=F1 and F2>=F3 then F2
end as F4
from
(select case when ISNUMERIC(isnull(f1,0))=0 then 0 else isnull(f1,0) end as f1,
case when ISNUMERIC(isnull(f2,0))=0 then 0 else isnull(f2,0) end as f2,
case when ISNUMERIC(isnull(f3,0))=0 then 0 else isnull(f3,0) end as f3
from A) T) M
where T.F1+T.F2+T.F3=M.F1+M.F2+M.F3

[prcgolf]

up

[dulei115]

我觉得用函数会好一点
create function MaxNum(@f1 nvarchar(10), @f2 nvarchar(10), @f3 nvarchar(10)) returns nvarchar(10)
begin
declare @n nvarchar(10)
set @n = '0'
if isnumeric(@f1) = 1
set @n = @f1
if isnumeric(@f2) = 1
if cast(@f2 as numeric(20,10)) > cast(@n as numeric(20,10))
set @n = @f2
if isnumeric(@f3) = 1
if cast(@f3 as numeric(20,10)) > cast(@n as numeric(20,10))
set @n = @f3
return @n
end
---------------------------------------------------------
update A set f4 = MaxNum(f1,f2,f3)

[zgtt]

谢谢大家指教
问题解决了.
结贴:)