sql - SQL:不工作时的情况
问题描述
我正在尝试返回“增加的调整”或“减少的调整”,具体取决于“金额”列中的值是正值还是负值。
CASE amount
when amount > 0 then "Increase Adjustment"
when amount < 0 then "Decreased Adjustment"
else "ERROR"
end as
transaction_type
任何建议将不胜感激...
编辑:整个选择=:
select
tx_date,
bank_account,
description,
amount,
currency,
CASE bank_account
when 'CAD-FTX' then 'Suspense_CAD'
when 'USD-PRO' then 'Suspense_USD'
when 'CAD-PRO' then 'Suspense_CAD'
when 'USD-ALL' then 'Suspense_USD'
when 'TD-USA' then 'Suspense_USD'
end
suspense_account,
CASE amount
when cast(amount as float) > 0 then "Increase Adjustment"
when cast(amount as float) < 0 then "Decreased Adjustment"
end
transaction_type
from [sys_tx_combined]
解决方案
这是编写表达式的更简单方法:
select tx_date, bank_account, description, amount, currency,
(case when bank_account in ('CAD-FTX', 'CAD-PRO')
then 'Suspense_CAD'
when bank_account in ('USD-PRO', 'USD-ALL', 'TD-USA')
then 'Suspense_USD'
end) suspense_account,
(case when amount > 0 then 'Increase Adjustment'
when amount < 0 then 'Decreased Adjustment'
end) as transaction_type
from [sys_tx_combined];
笔记:
- 在使用
case
此类映射的表达式时,我更喜欢为每个输出条件设置一个条件,而不是为每个输入设置一个条件。 - 几乎可以肯定是没有必要的
cast(amount as float)
,所以我删除了它。 - 对字符串常量使用单引号,即使您的数据库支持双引号(应该用于其他目的)。