sql - 如何处理由 LEFT JOIN 创建的重复项

Question

左表:

+------+---------+--------+
| Name | Surname | Salary |
+------+---------+--------+
| Foo  | Bar     |    100 |
| Foo  | Kar     |    300 |
| Fo   | Ba      |     35 |
+------+---------+--------+

右表:

+------+-------+
| Name | Bonus |
+------+-------+
| Foo  |    10 |
| Foo  |    20 |
| Foo  |    50 |
| Fo   |    10 |
| Fo   |   100 |
| F    |  1000 |
+------+-------+

期望的输出:

+------+---------+--------+-------+
| Name | Surname | Salary | Bonus |
+------+---------+--------+-------+
| Foo  | Bar     |    100 |    80 |
| Foo  | Kar     |    300 |     0 |
| Fo   | Ba      |     35 |   110 |
+------+---------+--------+-------+

我得到的最接近的是:

SELECT 
    a.Name,
    Surname,
    sum(Salary),
    sum(Bonus)
FROM (SELECT 
        Name,
        Surname,
        sum(Salary) as Salary
      FROM input
      GROUP BY 1,2) a LEFT JOIN (SELECT Name,
                                        SUM(Bonus) as Bonus
                                 FROM input2
                                 GROUP BY 1) b 
ON a.Name = b.Name
GROUP BY 1,2;

给出:

+------+---------+-------------+------------+
| Name | Surname | sum(Salary) | sum(Bonus) |
+------+---------+-------------+------------+
| Fo   | Ba      |          35 |        110 |
| Foo  | Bar     |         100 |         80 |
| Foo  | Kar     |         300 |         80 |
+------+---------+-------------+------------+

我不知道如何摆脱 Bonus复制。对我来说理想的解决方案是在“DESIRED OUTPUT”中指定的，它添加了 Bonus只有一个Name以及具有相同 Name 的其他记录添加0 .

Answer 1

您可以使用row_number():

select l.*, (case when l.seqnum = 1 then r.bonus else 0 end) as bonus
from (select l.*, row_number() over (partition by name order by salary) as seqnum
      from "left" l
     ) l left join
     (select r.name, sum(bonus) as bonus
      from "right" r
      group by r.name
     ) r
     on r.name = l.name