加入给出错误的总数

问题描述：

我正在尝试编写一个select语句，为每个名称收集一行。预计产量因此：加入给出错误的总数

名称=铝，工资= 30，奖金= 10

TABLE_1

Name Salary 
Al  10 
Al  20

TABLE_2

Name Bonus 
Al 5 
Al 5

我怎么写？我尝试：

Select t1.Name, SUM(t1.Salary), SUM(t2.Bonus) FROM table_1 t1 
LEFT JOIN table_2 t2 
ON t1.Name=t2.Name 
Group By 1

我得到奖金20而不是10奖金。这可能是因为在t1中有两行从中总结了奖金。我如何修改我的功能以获得正确的奖金？

一个提示，做'SELECT * FROM TABLE_1 t1 LEFT JOIN table_2 t2 ON t1.Name = t2.Name'。 – jarlh

答

集团的表分别由员工，然后加入他们的行列：

SELECT t1.Name, Salary, Bonus 
FROM (
    SELECT Name, SUM(Salary) Salary 
    FROM table_1 
    GROUP BY Name 
) t1 
LEFT JOIN (
    SELECT Name, SUM(Bonus) Bonus 
    FROM table_2 
    GROUP BY Name 
) t2 ON t1.Name = t2.Name

您需要在选择列表中限定名称。 – jarlh

谢谢。修复。 – Codo

答

你可以用这样一个子查询做到这一点：

declare @salary table (Name varchar(100), value int) 
declare @bonus table (Name varchar(100), value int) 

insert into @salary 
values ('al', 10) 

insert into @salary 
values ('al', 20) 

insert into @bonus 
values ('al', 5) 

insert into @bonus 
values ('al', 5) 


select s.Name, sum(value) as Salary, Bonus 
from @salary s JOIN 
(
    select Name, sum(value) as Bonus 
    from @bonus 
    group by Name 
) b on b.name = s.Name 
group by s.Name, b.Bonus

答

SELECT 
    coalesce(t1.name,t2.name) name, 
    coalesce(sum(t1.salary),0) salary_total, 
    coalesce(sum(t2.bonus),0) bonus_total 
FROM 
    (select name, sum(salary) salary from salary_table group by 1) t1 
FULL OUTER JOIN 
    (select name, sum(bonus) bonus from bonus_table group by 1) t2 
ON (t1.name=t2.name) 
GROUP BY 1 
;

加入给出错误的总数

相关推荐