我有下面的表格。以工资为条件,我想获得多行。 下面是当前的表格叫它雇员。
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 14000compare 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 EndDesired 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 102I 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.empidIf 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 ...更多推荐
发布评论