时间戳

首页 > 解决方案 > 如何将 2 个表和 if 子句合并到一列中

sql - 如何将 2 个表和 if 子句合并到一列中

问题描述

我在使用 SQL 并尝试在 APEX（交互式网格）中组合 2 个表方面非常新，基于薪水和从表 X（我管理）中获取的信息，但我需要在同一个 SQL 语句中基于来自列 Type 的信息，以在列百分比 if 子句中创建信息。

并且也在同一个 SQL 语句中得到总数，这将是（率小时百分比）。

但似乎我无法正确地将这么多代码组合成一个，我已经根据我发现的所有示例尝试了不同的方法，但它似乎由于某种原因不起作用。

select 
Overtime.ID,
OVERTIME.EMPLOYEE_NUMBER,
OVERTIME.EMPLOYEE_FULL_NAME,
OVERTIME."DATE",
OVERTIME.TYPE,
OVERTIME.HOURS,
EMPLOYEES.RATE,
EMPLOYEES.SALARY,
OVERTIME.PERCENTAGE (CASE
WHEN TYPE = "On Call " THEN "70%"
WHEN TYPE = "On Call PH" THEN "100%"
ELSE "150%"
END),
(Rate*hours*percentage) as total,
OVERTIME.CREATED,
OVERTIME.CREATED_BY
from OVERTIME 
LEFT OUTER JOIN EMPLOYEES
ON OVERTIME.EMPLOYEE_NUMBER = EMPLOYEES.EMPLOYEE_NUMBER
group by Employee_Number

select 
Overtime.ID,
OVERTIME.EMPLOYEE_NUMBER,
OVERTIME.EMPLOYEE_FULL_NAME,
OVERTIME."DATE",
OVERTIME.TYPE,
OVERTIME.HOURS,
EMPLOYEES.RATE,
EMPLOYEES.SALARY,
OVERTIME.PERCENTAGE,
(Rate*hours*percentage) as total,
OVERTIME.CREATED,
OVERTIME.CREATED_BY
from OVERTIME 
LEFT OUTER JOIN EMPLOYEES
ON OVERTIME.EMPLOYEE_NUMBER = EMPLOYEES.EMPLOYEE_NUMBER
WHERE OVERTIME.TYPE LIKE 
 (CASE
   WHEN TYPE = "On Call " THEN "70%"
   WHEN TYPE = "On Call PH" THEN "100%"
   ELSE "150%"
 END);
group by Employee_number

加班表如下：

ID Number primary Key
Employee_number vchar2
employee_full_name vchar2
date date
type vchar2
hours
salary number
rate number
percentage vachar2
total number
created timestamp
created_by vchar2

对于员工表

ID number primary key
Employee_number vchar2
employee_full_name vchar2
salary number
rate number

我需要有百分比信息以反映在我认为应该与 if 子句一起使用的百分比列中

标签： sqlif-statementleft-joinoracle-apex

解决方案

您可以使用 SQL CASE 语句计算 select 语句中的总数。

不确定这是计算总工资的正确方法 - 因为您希望将基本小时费率添加到百分比率，但您可以更改它。

select 
    Overtime.ID,
    OVERTIME.EMPLOYEE_NUMBER,
    OVERTIME.EMPLOYEE_FULL_NAME,
    OVERTIME."DATE",
    OVERTIME.TYPE,
    OVERTIME.HOURS,
    EMPLOYEES.RATE,
    EMPLOYEES.SALARY

    ,[Percentage] = CASE WHEN [Type] ='On Call ' THEN '70%'
                        WHEN [Type] = 'On Call PH' THEN '100%' 
                        ELSE  '150%'
                    END

    ,Total = CASE WHEN [Type] = 'On Call ' THEN (EMPLOYEES.RATE * OVERTIME.HOURS * 0.7)
                 WHEN [TYPE] = 'On Call PH THEN' THEN (EMPLOYEES.RATE * OVERTIME.HOURS)
                 ELSE  (EMPLOYEES.RATE * OVERTIME.HOURS * 1.5)
            END

    ,OVERTIME.CREATED
    ,OVERTIME.CREATED_BY

from OVERTIME  LEFT OUTER JOIN EMPLOYEES ON OVERTIME.EMPLOYEE_NUMBER = EMPLOYEES.EMPLOYEE_NUMBER
group by Employee_Number

推荐阅读