按主键,主键和从属列分组

CREATE TABLE T1 (a int primary key, b int);

SELECT a, b FROM T1 GROUP BY a;

--Msg 8120级别16。在选择列表中,列“ T1.b”无效,因为该列既未包含在聚合函数中,也未包含在GROUP BY子句中。

我本来希望这行得通,因为列b显然是从属列,因此按a和b进行分组显然与按a进行分组相同。

我正在使用SQL Server 2016。

评论
  • 离别yi刻
    离别yi刻 回复

    SQL Server does not support dependent columns in GROUP BY. All databases differ from standard specifications in some respects. So, although what you want to do is allowed in the standard, not all databases support the functionality.

    只需使用聚合函数:

    SELECT a, MAX(b) as b
    FROM T1
    GROUP BY a;
    

    Or include it in the GROUP BY:

    SELECT a, b
    FROM T1
    GROUP BY a, b;
    

    And I should also point out that the GROUP BY is unnecessary in this case. I suspect that this is in reference to more complicated queries where it would be appropriate.

  • Carr
    Carr 回复

    如果您需要不同的值,请使用DISTINCT而不是分组依据

    从T1选择不同的a,b

    否则..对group by子句中未提及的列使用适当的聚合函数