当我按 PK 分组时，该表中的所有列在每个组中都具有相同的值.那么为什么 SQL Server 需要对这些列进行聚合呢

前言

在SQL中使用GROUPING有一条一般规则:如果要在SELECT子句中引用列，则必须将其包装在聚合函数中，或者必须将其包括在GROUP BY子句中.在理解这一规则和它的原因.我的问题是关于一种特殊情况，我认为这条规则应该有一个例外:当两个或多个表连接在一起并按一个表的主键进行分组时.

考虑两个表(TABLE_A和TABLE_B).如果我对TABLE_A的主键进行分组，则属于TABLE_A的任何其他列在每个组中都将具有相同的值.情况必然是这样的.事情一直都是这样.我觉得SQL Server应该允许我包括TABLE_B中的其他列，而不需要将它们包装在AGG函数中.

以下是我想要做的事情:

SELECT
    AVG(B.score) AS average_score
    ,A.f_name
    ,A.l_name
FROM table_A A
JOIN table_B B ON B.table_A_id = A.id
GROUP BY
    A.id

相反，我得到了这个错误:

列‘TABLE_A.F_NAME’在 Select 列表中无效，因为它是 不包含在聚合函数或GROUP BY子句中.

我想过解决办法，但我对其中任何一个都不满意.

解决方法1-过度聚合

SELECT
    AVG(B.score) AS average_score
    ,MIN(A.f_name) AS first_name
    ,MIN(A.l_name) AS last_name
FROM table_A A
JOIN table_B B ON B.table_A_id = A.id
GROUP BY
    A.id

结果:

average_score first_name last_name
------------- ---------- ----------
12            Bob        Ross      
11            Ricky      Bobby     
12            Rick       Ross 

我不喜欢这样，因为它模糊了代码的含义；我们这里不是really取最小值.事实上，我们 Select 哪个agg函数并不重要:min()和max()都返回相同的内容.

SELECT
    AVG(B.score) AS average_score
    ,MAX(A.f_name) AS first_name
    ,MAX(A.l_name) AS last_name
FROM table_A A
JOIN table_B B ON B.table_A_id = A.id
GROUP BY
    A.id

结果:

average_score first_name last_name
------------- ---------- ----------
12            Bob        Ross      
11            Ricky      Bobby     
12            Rick       Ross  

这突出了TABLE_A中的所有值在每个组中实际上都是相同的，以及为什么我认为不应该强制对TABLE_A列的引用使用AGG函数，因为TABLE_A的PK是分组的.

解决方法2-不必要的分组

在这里，我们按不带agg函数的SELECT子句中出现的每一列进行分组.

SELECT
    AVG(B.score) AS average_score
    ,A.f_name
    ,A.l_name
FROM table_A A
JOIN table_B B ON B.table_A_id = A.id
GROUP BY
    A.f_name
    ,A.l_name
    ,A.id

结果:

average_score f_name     l_name
------------- ---------- ----------
12            Bob        Ross      
11            Ricky      Bobby     
12            Rick       Ross      

再一次，我觉得代码的含义被模糊了.我们想要的是让每个人都成为他们自己的一组人.然而，这读起来就像是我们根据共享的名字将每个人分成组，然后根据姓氏进一步划分这些组.由于有些人可能有相同的名字和姓氏，我们仍然认为有必要按PK进行分组，以确保每个组都有一个人.然而，如果我们一开始就按PK分组，我们就可以一口气做到这一点.这在语法上是低效的(不确定实际性能是否更差，但它看起来像是在做一些额外的工作--不是直接的).

解决方法3-冗余子查询

在这里，我们在SELECT子句中有子查询，而不是连接.

SELECT
    AVG(B.score) AS average_score
    ,(SELECT A.f_name FROM table_A A WHERE A.id = B.table_A_id) AS first_name
    ,(SELECT A.L_name FROM table_A A WHERE A.id = B.table_A_id) AS last_name
FROM table_B B
GROUP BY
    B.table_A_id

结果

average_score first_name last_name
------------- ---------- ----------
12            Bob        Ross      
11            Ricky      Bobby     
12            Rick       Ross      

我也不喜欢这样，因为我们必须为TABLE_A中要返回的每一列重复几乎相同的子查询.表A和表B正在恳求加入.我们希望结果集中的数据越多，问题就越严重，特别是当我们需要遍历TABLE_A来获取数据时.

SELECT
    AVG(B.score) AS average_score
    ,(SELECT A.f_name FROM table_A A WHERE A.id = B.table_A_id) AS first_name
    ,(SELECT A.L_name FROM table_A A WHERE A.id = B.table_A_id) AS last_name
    ,(SELECT C.team FROM table_C C WHERE C.id = (SELECT A.table_C_id FROM table_A A WHERE A.id = B.table_A_id)) AS team_name
FROM table_B B
GROUP BY
    B.table_A_id

结果:

average_score first_name last_name  team_name
------------- ---------- ---------- ----------
12            Bob        Ross       Team A    
11            Ricky      Bobby      Team A    
12            Rick       Ross       Team B    

下面是相同的逻辑查询，但使用了更早的解决方法:

SELECT
    AVG(B.score) AS average_score
    ,MIN(A.f_name) AS first_name
    ,MIN(A.l_name) AS last_name
    ,MIN(C.team) AS team_name
FROM table_A A
JOIN table_B B ON B.table_A_id = A.id
JOIN table_C C ON C.id = A.table_C_id
GROUP BY
    A.id

结果:

average_score first_name last_name  team_name
------------- ---------- ---------- ----------
12            Bob        Ross       Team A    
11            Ricky      Bobby      Team A    
12            Rick       Ross       Team B    

你们都觉得这同样令人沮丧吗？有没有人有比我想到的更优雅的解决办法？其他区议会是否也有同样的问题？