从单列值的条件中选择具有多行的语句 [英] 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屋！