如何在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

我想获取员工有资格获得每个 leave_type(category) 的假期数.以下查询 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

现在我想加入这 2 个查询 Query1 和 Query2 或创建显示两个查询记录的视图.此外，正如您从输出中看到的那样，没有不同的编号.来自不同查询的记录.对于在 query1 的输出中不存在的记录，它应该在最终输出中显示 0. 就像在本例中一样，query1 的输出中没有像 privlage 这样的记录，但它应该在 Sum(time_period) 中显示 0) 在最终输出的特权中.我尝试创建这 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'; 

视图 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: invalid identifier".我也知道我不能使用 union，因为它需要相同的列，而这里不是.

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屋！