select 和case when then 语句的嵌套 高手进 急

[zhenshz]

select a,b,
case
When cast(datediff(day,b,getdate()) as float) /100>1
Then replace(a,substring(A,Charindex('@1@',A)+len('@1@') ,charindex('&1&',A)-Charindex('@1@',A)-len('@1@')),0.0000)
When cast(datediff(day,b,getdate()) as float) /100<=1
Then replace(a,substring(A,Charindex('@1@',A)+len('@1@') ,charindex('&1&',A)-Charindex('@1@',A)-len('@1@')),1.0000-(convert(decimal(18,4),cast(datediff(day,b,getdate()) as float)/100,0)))
End
From qq
我要在这个基础上加空值判断，如a=''是用@1@、、&1&进行组合中间的数若超过100（cast(datediff(day,b,getdate()) as float) /100>1）用0.0000代替，否则用按1.0000-（cast(datediff(day,b,getdate()) as float) /100）


还有一种情况是a有字符，但是字符没有@1@、&1& 这种规律，也同样要把它组合

[cd731107]

select a,b,
case when a='' then   
  case when cast(datediff(day,b,getdate()) as float) /100>1  
  then '@1@'+cast(datediff(day,b,getdate()) as float)/100+'.0000'+'&1&'
  when cast(datediff(day,b,getdate()) as float) /100<=1   
  then '@1@'+replace(a,1.0000-(convert(decimal(18,4),cast(datediff(day,b,getdate()) as float)/100,0)),'')+'&1&'
	end
end
 from qq

[zhenshz]

我上面那个字段没说清楚，a字段是adfsda@1@0.002&1&aasdf@2@0.0000&2&AAAA,BBB,VVVVV
asdfasdf@1@0.0020&1&asf@2@0.0000&2&111111V
asdf@1@0.0020&1&@2@0.0000&2&222222223
b字段是2011-05-14 00:00:00
2007-09-19 00:00:00
2005-02-14 00:00:00

[zhenshz]

[Quote=引用 6 楼 acherat 的回复:]
select a,b
case when a='' then
case when cast(datediff(day,b,getdate()) as float) /100>1
then '@1@'+cast(datediff(day,b,getdate()) as float)/100+'.0000'+'&1&'
when cast(datediff(……
[/Quote]


就是我要求出系统当前时间和b字段的时间差，插入到为a字段为空的列上，并按照@1@+时间差+&1&这种规律组合查找出来，时间差要求超过一百天的用0.0000表示，没超过一百天的用1.0000-时间差，插入到a字段中，还有一种情况是当a字段不为空，a有数据但没有@1@、、$1$这种规律时按照上面的要求组合起来

[-晴天]

直接再增加 when then 不行么?

[gw6328]

a 处case when
或最外面再case when
不知道楼主是不是这个意思。

[zhenshz]

[Quote=引用 1 楼 qianjin036a 的回复:]
直接再增加 when then 不行么?
[/Quote]

select a,b
case when a='' then
case when cast(datediff(day,b,getdate()) as float) /100>1
then '@1@'+cast(datediff(day,b,getdate()) as float)/100+'.0000'+'&1&'
when cast(datediff(day,b,getdate()) as float) /100<=1
then '@1@'+replace(a,1.0000-(convert(decimal(18,4),cast(datediff(day,b,getdate()) as float)/100,0)))+'&1&'
end
from qq


这是我写的，可是一直提示语法错误

[zhenshz]

[Quote=引用 2 楼 jinfengyiye 的回复:]
a 处case when
或最外面再case when
不知道楼主是不是这个意思。
[/Quote]


对，就是case里面再加case

[zhenshz]

adfsda@1@0.002&1&aasdf@2@0.0000&2&AAAA,BBB,VVVVV
asdfasdf@1@0.0020&1&asf@2@0.0000&2&111111V
asdf@1@0.0020&1&@2@0.0000&2&222222223


这是a 里面的字段
2011-05-14 00:00:00
2007-09-19 00:00:00
2005-02-14 00:00:00

[AcHerat]

select a,b
case when a='' then
case when cast(datediff(day,b,getdate()) as float) /100>1
then '@1@'+cast(datediff(day,b,getdate()) as float)/100+'.0000'+'&1&'
when cast(datediff(day,b,getdate()) as float) /100<=1
then '@1@'+replace(a,1.0000-(convert(decimal(18,4),cast(datediff(day,b,getdate()) as float)/100,0)))+'&1&'
end

end

from qq

楼主看懂意思呢？