有效地包含不在SQL查询的Group By中的列

Given

特定

Table A

表A.

Id   INTEGER
Name VARCHAR(50)

Table B

表B.

Id   INTEGER
FkId INTEGER  ; Foreign key to Table A

I wish to count the occurrances of each FkId value:

我想计算每个FkId值的出现次数：

SELECT FkId, COUNT(FkId) 
FROM B 
GROUP BY FkId

Now I simply want to also output the Name from Table A.

现在我只想输出表A中的Name。

This will not work:

这不起作用：

SELECT FkId, COUNT(FkId), a.Name
FROM B b
INNER JOIN A a ON a.Id=b.FkId
GROUP BY FkId

because a.Name is not contained in the GROUP BY clause (produces is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause error).

因为a.Name不包含在GROUP BY子句中（生成在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句错误中）。

The point is to move from output like this

重点是从这样的输出转移

FkId  Count
1      42
2      25

to output like this

输出像这样

FkId  Count  Name
1      42     Ronald
2      22     John

There are quite a few matches on SO for that error message, but some e.g. https://*.com/a/6456944/141172 have comments like "will generate 3 scans on the table, rather than 1, so won't scale".

对于该错误消息，在SO上存在相当多的匹配，但是例如https://*.com/a/6456944/141172有一些评论，如“将在桌面上生成3次扫描，而不是1，因此无法扩展”。

How can I efficiently include a field from the joined Table B (which has a 1:1 relationship to FkId) in the query output?

如何在查询输出中有效地包含联接表B中的字段（与FkId具有1：1的关系）？

3 个解决方案

#1

You can try something like this:

你可以尝试这样的事情：

   ;WITH GroupedData AS
   (
       SELECT FkId, COUNT(FkId) As FkCount
       FROM B 
       GROUP BY FkId
   ) 
   SELECT gd.*, a.Name
   FROM GroupedData gd
   INNER JOIN dbo.A ON gd.FkId = A.FkId

Create a CTE (Common Table Expression) to handle the grouping/counting on your Table B, and then join that result (one row per FkId) to Table A and grab some more columns from Table A into your final result set.

创建一个CTE（公用表表达式）来处理表B上的分组/计数，然后将该结果（每个FkId一行）连接到表A，并从表A中获取更多列到最终结果集中。

#2

Did you try adding the field to the group by?

您是否尝试将该字段添加到该组中？

SELECT FkId, COUNT(FkId), a.Name
FROM B b
INNER JOIN A a ON a.Id=b.FkId
GROUP BY FkId,a.Name

#3

select t3.Name, t3.FkId, t3.countedFkId from (a t1 
  join (select t2.FkId, count(FkId) as countedFkId from b t2 group by t2.FkId) 
  on t1.Id = t2.FkId) t3;

#1