对同一列执行不同的计算

Question

我有一个工资列，我想给所有经理加薪20%，给所有副总裁加薪30%。我不确定如何将表连接到自己，然后进行不同的计算，因为select语句将保持不变，但加薪20%

SELECT 'Emp# ' || EMPLOYEE_ID  || ' named ' || FIRST_NAME || ' ' ||LAST_NAME || ' who is ' || JOB_ID || ' will have a new salary of $' || (SALARY +(SALARY*0.2)) AS "Employee with increased pay"
FROM EMPLOYEES
WHERE JOB_ID LIKE '___MAN%' OR (JOB_ID LIKE '___VP%') AND (SALARY < 6000 OR 
SALARY > 11000)
ORDER BY (SALARY - (SALARY*0.2)) DESC

Answer 1

您可以使用CASE子句。

SQL允许您在CASE语句中使用IF ... THEN ... ELSE逻辑，而不必调用过程。(参见Documentation)

您的查询应该如下所示：

SELECT 'Emp# '
  || EMPLOYEE_ID
  || ' named '
  || FIRST_NAME
  || ' '
  ||LAST_NAME
  || ' who is '
  || JOB_ID
  || ' will have a new salary of $'
  ||
  CASE JOB_ID
    WHEN 'Manager'
    THEN (SALARY +(SALARY*0.2))
    WHEN 'VP'
    THEN (SALARY +(SALARY*0.3))
  END AS "Employee with increased pay"
FROM EMPLOYEES
WHERE JOB_ID LIKE '___MAN%'
OR (JOB_ID LIKE '___VP%')
AND (SALARY < 6000
OR SALARY   > 11000)
ORDER BY (SALARY - (SALARY*0.2)) DESC

在CASE中，我使用了列JOB_ID的值，假设'Manager‘和'VP’是您的表的实际值。

希望它能为你工作。