如何在Oracle SQL中将具有不同数量的记录和列的2个查询联接在一起? [英] How to join 2 queries with different number of records and columns in oracle sql?

问题描述

我有三个表:

Employee_leave(EmployeeID,Time_Period,leave_type)  
Employee(EID,Department,Designation) 
 leave_eligibility(Department,Designation, LeaveType, LeavesBalance). 

我想获取每个LeaveTypes(Category)中特定雇员的休假数，所以我在查询Query1上写了以下内容

I want to fetch the number of leaves availed by a particular employee in each LeaveTypes(Category) so I wrote following query Query1

    SELECT LEAVE_TYPE, SUM(TIME_PERIOD) 
    FROM EMPLOYEE_LEAVE 
    WHERE EMPLOYEEID=78 
    GROUP BY LEAVE_TYPE 
    order by leave_type;

Query1的输出

Leave_Type     |    SUM(Time_Period)
Casual                         1
Paid                           4
Sick                           1

我想获取员工有资格离开每个离开类型(类别)的休假数量.以下查询Query2给出了所需的结果.

I want to fetch the number of leaves an employee is eligible for each leave_type(category). Following query Query2 gives the desire result.

    Select UNIQUE Leavetype,LEAVEBALANCE 
    from LEAVE_ELIGIBILITY 
          INNER JOIN EMPLOYEE 
              ON LEAVE_ELIGIBILITY.DEPARTMENT= EMPLOYEE.DEPARTMENT 
                 AND LEAVE_ELIGIBILITY.DESIGNATION= EMPLOYEE.DESIGNATION 
     WHERE EID=78
     order by leavetype;

Query2的输出

LeaveType     |    LeaveBalance
Casual                   10
Paid                     15           
Privlage                  6             
Sick                     20

现在，我想加入这两个查询Query1和Query2或创建显示两个查询的记录的视图.从输出中还可以看到有不同的编号.来自不同查询的记录. 对于在query1的输出中不存在的记录，它在最终输出中应该显示0.像在当前情况下一样，在Query1的输出中没有记录，例如privlage，但是它在Sum(time_period )在最终输出中.我尝试创建这2个查询的视图，然后将它们加入，但是我无法运行最终查询.

Now I want to join these 2 queries Query1 and Query2 or create view which displays records from both queries. Also as you can see from output there are different no. of records from different queries. For a record which is not present in output of query1, it should display 0 in final output. Like in present case there is no record in output of query1 like privlage but it should display 0 in Sum(time_period) in Privlage of final output. I tried creating views of these 2 queries and then joining them, but I'm unable to run final query.

视图1的代码

create or replace view combo_table1 as 
   Select UNIQUE Leavetype,LEAVEBALANCE,EMPLOYEE.DEPARTMENT,EMPLOYEE.DESIGNATION, EID 
  from LEAVE_ELIGIBILITY 
       INNER JOIN EMPLOYEE 
       ON LEAVE_ELIGIBILITY.DEPARTMENT= EMPLOYEE.DEPARTMENT
       AND LEAVE_ELIGIBILITY.DESIGNATION= EMPLOYEE.DESIGNATION 
        WHERE EID='78'; 

View 2的代码

create or replace view combo_table2 as
    SELECT LEAVE_TYPE, SUM(TIME_PERIOD) AS Leave_Availed 
    FROM EMPLOYEE_LEAVE
    WHERE EMPLOYEEID='78'
    GROUP BY LEAVE_TYPE;

用于加入2个视图的代码

Code for joining 2 views

SELECT combo_table1.Leavetype, combo_table1.LEAVEBALANCE, combo_table2.leave_availed 
FROM combo_table1 v1 
INNER JOIN combo_table2 v2 
ON v1.Leavetype = v2.LEAVE_TYPE;

但是在执行上述查询时，我得到了％s:无效标识符".我也知道我不能使用并集，因为它需要相同的列，而不是这里.

But I'm getting "%s: invalid identifier" while executing the above query. Also I know I can't use union as it requires same column which here it is not.

我正在使用Oracle 11g，因此请相应回答.

谢谢.

所需的最终输出

LeaveType  |  LeaveBalance   |   Sum(Time_period)
Casual               10                  1
Paid                 15                  4        
Privlage              6                  0
Sick                 20                  1

推荐答案

获取最终所需的输出...

To get the final desired output ...

对于查询1的输出中不存在的记录，它在最终输出中应显示0."

"For a record which is not present in output of query1, it should display 0 in final output. "

...使用外部联接将带薪休假记录绑定到其他表.对于员工未休的休假类型，这将给出零time_duration.

... use an outer join to tie the taken leave records to the other tables. This will give zero time_duration for leave types which the employee has not taken.

select emp.Employee_ID
       , le.leavetype
       , le.leavebalance 
       , sum (el.Time_Duration) as total_Time_Duration
from employee emp
     inner join leave_eligibility le
          on le.department= emp.department 
             and le.designation= emp.designation 
     left outer join Employee_leave el
           on el.EmployeeID = emp.Employee_ID
           and el.leave_type = le.leavetype        
group by emp.Employee_ID
       , le.leavetype
       , le.leavebalance 
       ;

您当前的问题:

我收到％s:无效标识符"

I'm getting "%s: invalid identifier"

您的视图具有对列EID的引用，尽管您发布的表中都没有该名称的列.同样，在Time_Duration和time_period之间也有混淆.

Your view has references to a column EID although none of your posted tables have a column of that name. Likewise there is confusion between Time_Duration and time_period.

更一般地说，如果对通用列使用完全相同的名称(例如，始终使用employee_id或employeeid，请不要砍或改)，您会发现生活变得相当容易.

More generally, you will find life considerably easier if you use the exact same name for common columns (i.e. consistently use either employee_id or employeeid, don't chop and change).

这篇关于如何在Oracle SQL中将具有不同数量的记录和列的2个查询联接在一起?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！