mysql - 如何从两个表生成总和，然后将结果连接在一起而不用 SQL 重复？

Question

更详细地解释一下-我有两个表 A 和 B。员工 ID 是两个表之间的公共(public)列。我需要连接这 2 个表来比较这 2 个表中的 FTE。

Sample table
Table A:
Emp_ID    |S     Period  |s   FTE   
1                201701      0.5
1                201701      0.5
2                201702      0.7
3                201702       1

Table B
Emp_ ID  |S    Period |s     FTE
1              201701         1
2              201702        0.5
3              201702        0.7

FTE 需要按期间和 emp_id 进行求和和分组这两个表需要连接，结果应如下所示

Emp_ID   Period      FTE (Table A)      FTE (Table B)
1              201701       1                           1
2              201702       0.7                       0.5
3              201702      1                             0.7

查询

select * from
( select f.Employee_SK , f.period_nk 
  from adj.PayrollAdjustments p  
  cross join IA_FACT_Payroll f) pf
left join (select period_nk
         , employee_sk
         , sum(CalcClinical_FTE) as sum 
      from IA_FACT_Payroll 
     group 
        by Period_NK
        , Employee_SK ) f on pf.Employee_SK = f.Employee_SK

left join ( select LibPeriodKey
               ,PersonnelMasterKey
              , sum(clinicalFTE) as sumP
           from adj.PayrollAdjustments
           group by LibPeriodKey, PersonnelMasterKey) adj on pf.employee_sk = adj.personnelmasterkey

Answer 1

如果您不知道员工和期间的“主表”是否存在，那么您可能会假设工资表的摘要将涵盖所有员工和期间。如果这个假设成立，那么也许这会起作用:

select
*
from (
    select period_nk
         , employee_sk
         , sum(CalcClinical_FTE) as sum 
      from IA_FACT_Payroll 
     group 
        by Period_NK
        , Employee_SK
    ) iap
left join (
    select LibPeriodKey 
         , PersonnelMasterKey
         , sum(ClinicalFTE) as sumP 
      from adj.PayrollAdjustments 
     group 
        by LibPeriodKey
     , PersonnelMasterKey
    ) adj on iap.employee_sk = adj.PersonnelMasterKey and iap.period_nk = adj.LibPeriodKey

但是，如果有一个名为 employee_master 的员工主表，并且还有一个名为 periods_master 的期间主表，那么您可以交叉连接这些表，您会得到所有员工和所有期间的笛卡尔积允许您像这样外部连接计算:

例如

select e.id, p.id
from `employee_master` e
cross join `periods_master` p

从这样的员工和时间段来源中，您可以简单地外部连接 2 个查询，如下所示:

select
*
from (
    select e.id e_id, p.id p_id
    from `employee_master` e
    cross join `periods_master` p
     ) ep
left join (
    select period_nk
         , employee_sk
         , sum(CalcClinical_FTE) as sum 
      from IA_FACT_Payroll 
     group 
        by Period_NK
        , Employee_SK
    ) iap on ep.e_id = iap.Employee_SK and ep.p_id = iap.Period_NK
left join (
    select LibPeriodKey 
         , PersonnelMasterKey
         , sum(ClinicalFTE) as sumP 
      from adj.PayrollAdjustments 
     group 
        by LibPeriodKey
     , PersonnelMasterKey
    ) adj on ep.e_id = adj.PersonnelMasterKey and ep.p_id = adj.LibPeriodKey