从单列值的条件中选择具有多行的语句 [英] Select statement with multiple rows from condition on values in single column
问题描述
我有下表.以薪水为条件,我想获得多行. 下面是当前表,将其称为雇员.
I have the table below.Using salary as condition I want to get multiple rows. Below is current table call it employee.
empid name salary
-----------------------------------
1 A1 alex 20000
2 B2 ben 4500
3 C1 carl 14000
将薪水与某些固定值进行比较,每当薪水大于固定值时,在输出中显示一条记录.我的尝试条件情况与此相似:
compare the salary to certain fixed values, and every time the salary is larger than the fixed value, show a record in output.My attempt condition case is close to this:
incometype= case When salary<6000 then 101 When salary Between 6000 And 18000 Then
102 Else 103 End
期望的输出为:
empid name salary incometype
------------------------------------------
1 A1 alex 20000 101
2 A1 alex 20000 102
3 A! alex 20000 103
4 B2 ben 4500 101
5 C1 carl 14000 101
6 C1 carl 14000 102
我尝试使用union,但是即使值满足第一个条件,union也会为每条记录提供3行.
I have tried using union but union will give me 3 rows for each record even when value meets 1st condition.
推荐答案
您的问题尚不清楚,因为您的逻辑暗示您对于3个输入行只应有3个输出行.但是,您的输出表示您希望将薪水与某些固定值进行比较,并且每当薪水大于固定值时,就在输出中显示一条记录.
Your question is unclear, because your logic implies that you should only have 3 output rows for 3 input rows. Your output however implies that you want to compare the salary to certain fixed values, and every time the salary is larger than the fixed value, show a record in output.
如果是前者,那么Minh的查询就是您所需要的.在后一种情况下,您可以执行以下操作:
If the former is the case, Minh's query is all you need. In the latter case, you can do something like this:
select e.*, m.incometype
from employee e
left join
(
select 0 as threshold, 101 as incometype
union
select 5999 as threshold, 102 as incometype
union
select 17999 as threshold, 103 as incometype
) m
on e.salary > m.threshold
order by e.empid
如果要添加计算列,即在查询中使用列计算值的列,则可以将其简单地添加为select
子句中的列,如下所示:
If you want to add a calculate column i.e. one with values calculated using columns in this query, you can simply add it as a column in the select
clause, like so:
select e.*,
m.incometype,
case
when <first condition> then <business logic here>
....
else <handle default case>
end as yourcomputedcolumn
from
...
这篇关于从单列值的条件中选择具有多行的语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!