有效地包括不在SQL查询分组依据中的列 [英] Efficiently Include Column not in Group By of SQL Query

问题描述

给出

表A

Id   INTEGER
Name VARCHAR(50)

表B

Id   INTEGER
FkId INTEGER  ; Foreign key to Table A

我希望计算每个 FkId <的出现次数/ code>值：

I wish to count the occurrances of each FkId value:

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

现在我只想从表A <中输出名称/ code>。

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

这将不起作用：

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

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

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).

关键是要从这样的输出中移出

The point is to move from output like this

FkId  Count
1      42
2      25

输出这样的

FkId  Count  Name
1      42     Ronald
2      22     John

该错误消息在SO上有很多匹配项，例如 https://stackoverflow.com/a/6456944/141172 带有如下注释：将在表上生成3次扫描，大于1，因此无法缩放。

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

如何有效地 包含联接的表B中的字段（与 FkId 具有1：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?

推荐答案

您可以尝试如下操作：

   ;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

创建CTE （公用表表达式）来处理表B 上的分组/计数，然后加入该结果（每个 FkId ）到表A ，并从表A 中获取更多列作为最终结果设置。

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.

这篇关于有效地包括不在SQL查询分组依据中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！