从单个列中的值中选择具有多个条件的语句(Select statement with multiple rows from condition on values in single column)

从单个列中的值中选择具有多个条件的语句(Select statement with multiple rows from condition on values in single column)

我有下面的表格。以工资为条件，我想获得多行。 下面是当前的表格叫它雇员。

empid name salary ----------------------------------- 1 A1 alex 20000 2 B2 ben 4500 3 C1 carl 14000

将工资与某些固定值进行比较，并且每次工资大于固定值时，都会在输出中显示一条记录。我尝试的条件案例接近于此：

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，但即使值符合1st条件，union也会为每条记录提供3行。

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

Desired ouput would be:

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

I have tried using union but union will give me 3 rows for each record even when value meets 1st condition.

最满意答案

你的问题还不清楚，因为你的逻辑意味着你应该只有3个输出行用于3个输入行。 但是，您的输出意味着您要将薪水与某些固定值进行比较，并且每次薪水大于固定值时，都会在输出中显示一条记录。

如果前者是这种情况，Minh的查询就是你所需要的。 在后一种情况下，你可以这样做：

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子句中，如下所示：

select e.*, m.incometype, case when <first condition> then <business logic here> .... else <handle default case> end as yourcomputedcolumn from ...

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.

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

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 ...