按两个连接的列分组

CREATE TABLE T1 (a int);

CREATE TABLE T2 (a int);

SELECT T1.a , T2.a
FROM T1
JOIN T2 ON T1.a=T2.a
GROUP BY T1.a;

-消息8120级别16。在选择列表中,列'T2.a'无效,因为该列既不包含在聚合函数中,也不包含在GROUP BY子句中。

我本以为这会起作用,因为T1.a和T2.a已加入

评论
  • mut
    mut 回复
    我本以为这会起作用,因为T1.a和T2.a已加入

    The SQL Standard states that when there is dependency between columns of the same table, there's no need to aggregate the dependent ones. For example if you aggregate by the primary key, you don't need to aggregate other columns of the same table. However this is not the case, since the T2.a is on a different table. Nevertheless, SQL Server does not implement that part of the standard anyway.

    现在,只需稍作更改即可:

    SELECT T1.a , T2.a
    FROM T1
    JOIN T2 ON T1.a=T2.a
    GROUP BY T1.a , T2.a;
    

    See running example at DB Fiddle.

  • rqui
    rqui 回复

    Just add t2.a column in GROUP BY:

    SELECT 
    T1.a , T2.a
    FROM T1
    JOIN T2 ON T1.a= T2.a
    GROUP BY T1.a, T2.a;
    

    It is necessary to add t2.a as when SQL Server groups the same rows by t1.a, it does not know what column should be chosen for t2.a.

  • Ethel
    Ethel 回复
    我本以为这会起作用,因为T1.a和T2.a已加入

    This makes sense, but unfortunately: no. SQL Server sees two different unaggregated columns in the SELECT clause, and wants them in the GROUP BY clause too:

    SELECT T1.a, T2.a
    FROM T1
    JOIN T2 ON T1.a = T2.a
    GROUP BY T1.a, T2.a
    

    另一方面,由于您要加入这些列,因此您还知道两者具有相同的值,那么为什么需要在结果集中输出两者呢?

    SELECT T1.a
    FROM T1
    JOIN T2 ON T1.a = T2.a
    GROUP BY T1.a
    

    Some databases support the USING syntax on joins, which disambiguate the column names when both are the same - but not SQL Server:

    SELECT a
    FROM T1
    JOIN T2 ON USING(a)
    GROUP BY a