为每行选择x次而不使用union
是否可以更简单地编写如下所示的联合选择查询？
select
id,
1,
(1 + @defCapUp) * (p.Value + p.Premium),
getdate()
from Products p
union
select
id,
1,
(1 - @defCapDown) * (p.Value - p.Premium),
getdate()
from Products p
union
select
id,
case when p.Paydate > getdate() then 1 else 0 end,
(1 - @defCapUp) * (p.Value - p.Premium),
@nextYear
from Products p
union
select
id,
case when p.Paydate > getdate() then 1 else 0 end,
(1 + @defCapDown) * (p.Value + p.Premium),
@nextYear
from Products p
select
id,
1,
(1 + @defCapUp) * (p.Value + p.Premium),
getdate()
from Products p
union
select
id,
1,
(1 - @defCapDown) * (p.Value - p.Payout),
getdate()
from Products p
union
select
id,
case when p.Paydate > getdate() then 1 else 0 end,
(1 - @defCapUp) * (p.Value - p.Premium),
@nextYear
from Products p
union
select
id,
case when p.Paydate <= getdate() then 1 else 0 end,
(1 + @defCapDown) * (p.Value + p.Payout),
@nextYear
from Products p
没有找到相关结果
已邀请:
2 个回复
眠皇
select id, case when p.Paydate > X.CompareDate then 1 else 0 end, (1 + Cap) * (p.Value + ModF * p.Premium), @nextYear from Products p cross join ( select @defCapUp Cap, Cast(0 as datetime) CompareDate, 1 Modf union all select -@defCapDown, 0, -1 union all select -@defCapUp, GETDATE(), -1 union all select @defCapDown, GETDATE(), 1 ) X物崎巩
。SELECT id, field2, field3, field4 FROM Products p WHERE ( field4 = getdate() AND field2=1 AND ( field3=(1 + @defCapUp) * (p.Value + p.Premium) OR field3=(1 - @defCapDown) * (p.Value - p.Premium) ) ) OR ( field4=@nextYear AND field2=(case when p.Paydate > getdate() then 1 else 0 end) AND ( field3=(1 - @defCapUp) * (p.Value - p.Premium) OR field3=(1 + @defCapDown) * (p.Value + p.Premium) ) )