sql - 按最小值选择列 - 分组依据

Question

当我读到这篇文章时:
SQL: Group by minimum value in one field while selecting distinct rows

以下解决方案对我来说没有意义，我想知道它是如何工作的:

SELECT id, min(record_date), other_cols 
FROM mytable
GROUP BY id

这不适用于我的数据库，而且在我看来似乎不合逻辑(尽管它在 SQL fiddle 示例中确实有效)。我的 DBMS 提示说

Column must be group column: other_cols

我正在使用 MaxDB - 这是 DBMS 特定问题吗？

Answer 1

它适用于一些 RDBMS，如 MySQL . MySQL docs说:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group.

但在 的情况下SQL Server ，您需要包含 GROUP BY 中的所有字段条款。否则会报如下错误:

Column 'ColName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

见 MSDN .