sql - 根据员工是否在位加入同一张 table

Question

我有一个表格如下:

 year_id | loc_id | emp_id | freq 
 ---------------------------------
  1990   |   LA   | fred_01| 55   
  1990   |   LA   | mury_01| 34
  1990   |   BA   | fred_01| 20
  1990   |   CA   | mury_01| 40
  1990   |   MO   | fred_01| 100
  1990   |   PR   | bill_01| 500

我希望所有内容都按年份和地点分组，但棘手的部分我想得到每个位置所有“频率”的总和，然后也得到在该地点工作的所有员工“freq”的总和，当他们在另一个地点时(但不包括原始 loc 数量)。

前两行的期望结果:

 year_id | loc_id | sum(freq) loc | sum(freq)away 
 ------------------------------------------------
 1990       LA    |   89          |  160
 1990       BA    |   20          |  155 

我可以使用 INNER JOIN 一次做一个，但是当我尝试获得两个总和时，一切都变得一团糟:

select t1.loc_id , t1.year_id,sum(t2.freq),sum(t3.freq) from emp_freq t1
inner join emp_freq t2 
  on t1.year_id=t2.year_id and t1.emp_id=t2.emp_id and t1.loc_id=t2.loc_id 

inner join emp_freq t3 
on t1.year_id=t3.year_id and t1.emp_id=t3.emp_id and t1.loc_id<>t3.loc_id

group by t1.loc_id,t1.year_id
order by t1.loc_id,t1.year_id 

如果我注释掉第二个内部联接，则按 loc 分组的结果是正确的。

Answer 1

这是另一种解决方案，它使用两级聚合。

首先，按年份、地点和员工汇总记录，使用内联相关子查询计算员工今年在另一个地点花费的时间。

SELECT 
    year_id, 
    loc_id, 
    emp_id, 
    SUM(freq) freq,
    (SELECT SUM(freq) FROM emp_freq t1 WHERE t1.year_id = t1.year_id AND t1.emp_id = t.emp_id AND t1.loc_id <> t.loc_id) other_freq
FROM emp_freq t
GROUP BY year_id, emp_id, loc_id

然后，我们只需要再次对结果集进行分组，这次在非聚合列中没有员工，以计算总数:

SELECT year_id, loc_id, SUM(freq) freq, SUM(other_freq) other_freq
FROM (
    SELECT 
        year_id, 
        loc_id, 
        emp_id, 
        SUM(freq) freq,
        (SELECT SUM(freq) FROM emp_freq t1 WHERE t1.year_id = t1.year_id AND t1.emp_id = t.emp_id AND t1.loc_id <> t.loc_id) other_freq
    FROM emp_freq t
    GROUP BY year_id, emp_id, loc_id
) x GROUP BY year_id, loc_id

这个 demo on DB Fiddle 您的示例数据返回:

| year_id | loc_id | freq | other_freq |
| ------- | ------ | ---- | ---------- |
| 1990    | LA     | 89   | 160        |
| 1990    | BA     | 20   | 155        |
| 1990    | MO     | 100  | 75         |
| 1990    | CA     | 40   | 34         |
| 1990    | PR     | 500  |            |