一起选择和分组 [英] Select and Group by together

问题描述

我的查询是这样的:

Select 
  a.abc,
  a.cde,
  a.efg,
  a.agh,
  c.dummy
  p.test
  max(b.this)
  sum(b.sugar)
  sum(b.bucket)
  sum(b.something)

后跟一些外连接和内连接.现在的问题是分组时

followed by some outer join and inner join. Now the problem is when in group by

group by 
  a.abc,
  a.cde,
  a.efg,
  a.agh,
  c.dummy,
  p.test   

查询工作正常.但是，如果我从组中删除其中任何一个，它会给出:

The query works fine. But if I remove any one of them from group by it gives:

SQLSTATE: 42803

谁能解释这个错误的原因?

Can anyone explain the cause of this error?

推荐答案

一般情况下，任何不在group by部分的列只能包含在select 部分，如果它应用了聚合函数.或者，换一种方式，必须对 select 部分中的任何非聚合数据进行分组.

Generally, any column that isn't in the group by section can only be included in the select section if it has an aggregating function applied to it. Or, another way, any non-aggregated data in the select section must be grouped on.

否则，如何你知道你想用它做什么.例如，如果您对 a.abc 进行分组，则 a.abc 只能用于该分组行的一件事(因为 的所有其他值a.abc 将出现在不同的行中).这是一个简短的示例，其中包含一个表格:

Otherewise, how do you know what you want done with it. For example, if you group on a.abc, there can only be one thing that a.abc can be for that grouped row (since all other values of a.abc will come out in a different row). Here's a short example, with a table containing:

LastName  FirstName  Salary
--------  ---------  ------
Smith     John       123456
Smith     George     111111
Diablo    Pax        999999

通过查询select LastName, Salary from Employees group by LastName，您会看到:

LastName  Salary
--------  ------
Smith     ??????
Diablo    999999

Smiths 的薪水无法计算，因为您不知道要对其应用什么功能，而这正是导致该错误的原因.换句话说，DBMS 不知道如何使用 123456 和 111111 来为分组的行获取单个值.

The salary for the Smiths is incalculable since you don't know what function to apply to it, which is what's causing that error. In other words, the DBMS doesn't know what to do with 123456 and 111111 to get a single value for the grouped row.

如果您改为使用 select LastName, sum(Salary) from Employees group by LastName(或 max() 或 min() 或ave() 或任何其他聚合函数)，DBMS 将知道该做什么.对于sum()，它会简单地添加它们并给你234567.

If you instead used select LastName, sum(Salary) from Employees group by LastName (or max() or min() or ave() or any other aggregating function), the DBMS would know what to do. For sum(), it will simply add them and give you 234567.

在您的查询中，尝试在没有聚合函数的情况下使用 Salary 相当于将 sum(b.this) 更改为 b.this 但不包括在 group by 部分.或者，删除 group by 列之一，而不将其更改为 select 部分中的聚合.

In your query, the equivalent of trying to use Salary without an aggregating function is to change sum(b.this) to just b.this but not include it in the group by section. Or alternatively, remove one of the group by columns without changing it to an aggregation in the select section.

在这两种情况下，您都会有一行具有多个列的可能值.

In both cases, you'll have one row that has multiple possible values for the column.

db2 docs at publib for sqlstate 42803 描述了您的问题:

The DB2 docs at publib for sqlstate 42803 describe your problem:

SELECT 或 HAVING 子句中的列引用无效，因为它不是分组列；或者 GROUP BY 子句中的列引用无效.

A column reference in the SELECT or HAVING clause is invalid, because it is not a grouping column; or a column reference in the GROUP BY clause is invalid.

这篇关于一起选择和分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！