使用聚合(mssql / sql server)进行多表连接

Very simple question. What I want to do is select all columns from one table and sum of one column (which could have multiple matching rows) from another table.

非常简单的问题。我要做的是从一个表中选择所有列，从另一个表中选择一个列(可以有多个匹配行)的和。

Example:

例子:

table ta (eid, uid, name, year, etc, etc, etc)
table tb (eid, uid, name, year, amount, etc, etc)

eid - will not match between both table uid, name, year - will match across both tables

eid -两个表uid、名称、年份之间不匹配-将在两个表之间匹配

So I want to pull out all columns from table ta, simple:

我想从表ta中提取所有的列，很简单:

select * from ta where eid='value';

I want to join amount column from table tb to my resultset, simple:

我想将数量列从表tb连接到结果集，很简单:

select a.*, b.amount
from ta a
inner join tb b on a.year=b.year
where a.eid='value';

Great, this works fine. But what if I have multiple rows in table tb?

太好了,这个没问题。但是如果表tb中有多个行怎么办?

Executing:

执行:

select a.*, sum(b.amount)
from ta a inner join tb b on a.uid=b.uid
where a.year='value';

gives me the following error:

给我的错误如下:

Column 'ta.eid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

列的助教。eid'在select列表中无效，因为它不包含在聚合函数或GROUP BY子句中。

So I add:

所以我添加:

select a.*, sum(b.amount)
from ta a inner join tb b on a.uid=b.uid
where a.year='value' group by ta.uid;

And I get the same error!

我得到了同样的错误!

However, if I change my query to:

但是，如果我将查询改为:

select a.uid, a.year, sum(b.amount)
from ta a inner join tb b on a.uid=b.uid
where a.year='value'
group by ta.uid, ta.year;

It works, but now I have three columns instead of all columns that I wanted.

它是有效的，但是现在我有了三个列而不是我想要的所有列。

So, at this point my question becomes: Is there a better, cleaner way of structuring this query other than me manually typing out all columns I want to pull from two tables with GROUP BY clause?

因此，此时，我的问题变成了:除了手工输入我想从带有GROUP BY子句的两个表中提取的所有列之外，是否还有更好、更干净的方式来构造这个查询?

1 个解决方案

#1

You can preaggregate in a subquery:

您可以在子查询中预聚合:

select a.*, b.sumb
from ta a left join
     (select b.uid, sum(b.amount) as sumb
      from tb b
      group by b.uid
     ) b
     on a.uid=b.uid
where a.year = 'value';