英文:
Can I use the values from CASE expressions in other CASE expressions?
问题
SELECT
CASE -- result#1
WHEN .. then 'a'
WHEN .. then 'b'
END,
CASE -- result#2
WHEN .. then 'c'
WHEN .. then 'd'
END,
FROM table1
我上面有2个CASE表达式，我可以使用这两个CASE表达式的结果，创建另一个CASE表达式，根据这两个CASE表达式的值吗？
CASE
WHEN result1 IS NOT NULL and result2 IS NOT NULL THEN '...'
ELSE NULL
END
英文:
SELECT
CASE -- result#1
WHEN .. then 'a'
WHEN .. then 'b'
END,
CASE -- result#2
WHEN .. then 'c'
WHEN .. then 'd'
END,
FROM table1
I have 2 CASE expressions above, can I use the result of both CASE expressions and create another CASE expression based on the values from both CASE expressions?
CASE
WHEN result1 IS NOT NULL and result2 IS NOT NULL THEN '...'
ELSE NULL
END
答案1
得分: 1
无法在开发它的相同“块”中使用 case 表达式的结果。您需要使用子查询或通用表表达式（CTE）。
-- 使用子查询
select case
when result1 is not null
and result2 is not null
then '...'
else null
end "Final Result"
from ( select case
when .. then 'a'
when .. then 'b'
else null
end result1
, case
when .. then 'c'
when .. then 'd'
else null
end result2
from table1
) sq
-- 或者使用通用表表达式（CTE）
with first_case (result1, result2) as
( select case
when .. then 'a'
when .. then 'b'
else null
end
, case
when .. then 'c'
when .. then 'd'
else null
end
from table1
)
select case
when result1 is not null
and result2 is not null
then '...'
else null
end "Final Result"
from first_case;
请注意，代码部分未被翻译，仅翻译了注释和字符串。
英文:
You cannot use the result of a case expression at the same "block" that it is developed in. You need a sub select or a CTE.
select case
when result1 is not null
and result2 is not null
then '...'
else null
end "Final Result"
from ( select case
when .. then 'a'
when .. then 'b'
else null
end result1
, case
when .. then 'c'
when .. then 'd'
else null
end result2
from table1
) sq
OR
with first_case (result11, result22) as
( select case
when .. then 'a'
when .. then 'b'
else null
end
, case
when .. then 'c'
when .. then 'd'
else null
end
from table1
)
select case
when result1 is not null
and result2 is not null
then '...'
else null
end "Final Result"
from first_case;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论