带别名的案例陈述 [英] Case statements with alias

问题描述

我需要创建一个带有用户ID和分配值的表.我有以下三个选择语句:

I need to create a table with a user id and an assigned value. I have these three select statements:

select sales_person_id from promotions where 
sales > 30000 and city = ‘Georgia’ 

select sales_person_id from promotions
where sales > 50000 and city = ‘Atlanta’

select sales_person_id from promotions
where sales > 25000 and city = ‘Tampa’

基本上，我将需要它来显示是否选择语句一，该表将包含user_id和value = 10 如果选择语句两个user_id和value = 5 如果选择语句三个user_id和value = 7

Basically I would need it to show if select statement one, the table would contain user_id and value = 10 if select statement two user_id and value = 5 if select statement three user_id and value = 7

我尝试使用带有别名的case语句来获取名为value的列，但没有运气.任何帮助将不胜感激.

I have tried using case statements with an alias to get a column called value with no luck. Any help would be greatly appreciated.

推荐答案

只需将where子句中的条件转换为when表达式中的when条件:

Just convert the conditions in the where clauses to when conditions in a case expression:

SELECT sales_person_id,
       CASE WHEN sales > 30000 AND city = 'Georgia' THEN 10
            WHEN sales > 50000 and city = 'Atlanta' THEN 7
            WHEN sales > 25000 and city = 'Tampa'   THEN 5
       END AS value
FROM   promotions

这篇关于带别名的案例陈述的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！