将最大值计算列添加到 Where 子句?

Question

我为 trw1、trw2 和 trw3 创建了一个最大值计算列。但是，我无法让计算列在 where 子句中工作。

select *, s.knum as SPI_Knum, s.customer_id as Customer_id, s.season as SPI_Season,
s.inv as Inventory, s.owner_type as Room_type, s.status, s.trw1, s.trw2, s.trw21, 
b.[ln curr bal] as MTG_Bal,
(select max(HighTrw)
from (values (trw1), (trw2), (trw21)) As updatedate(HighTrw)
) as High_TRW
where m.knum is null
and d.knum is null
and st.customer_id is null
and b.[ln curr bal] between '15000' and '7500'
and max(HighTrw) between '625' and '600'  

我正在尝试使用它来设置标准。 所以基本上我需要显示任何值在 15000 到 7500 之间但计算行中 TRW 在 625 到 600 之间的内容。

Answer 1

为了成功使用where子句，您需要使用整个子查询：

select s.id, s.trw1, s.trw2, s.trw3, 
(select max(HighTrw)
from (values (trw1), (trw2), (trw3)) As updatedate(HighTrw))
as High_TRW
from my_table as s
where (select max(HighTrw)
from (values (trw1), (trw2), (trw3)) As updatedate(HighTrw)) between '600' and '625'