问题描述

我试图在没有聚合函数的情况下理解 GROUP BY (oracle dbms 的新手).
它是如何运作的?
这是我尝试过的.

我将在其上运行 SQL 的 EMP 表.

SELECT ename , sal发件人按名称分组

SELECT ename , sal发件人按名称分组；

<块引用>

结果

ORA-00979:不是 GROUP BY 表达式
00979. 00000 - 不是 GROUP BY 表达式"
*原因:
*动作:
行错误:397 列:16

SELECT ename , sal发件人按萨尔分组；

<块引用>

结果

ORA-00979:不是 GROUP BY 表达式
00979. 00000 - 不是 GROUP BY 表达式"
*原因:
*操作:错误行:411 列:8

SELECT empno , ename , sal发件人GROUP BY 萨尔 , 名字;

<块引用>

结果

ORA-00979:不是 GROUP BY 表达式
00979. 00000 - 不是 GROUP BY 表达式"
*原因:
*操作:错误行:425 列:8

SELECT empno , ename , sal发件人GROUP BY empno , ename , sal;

所以，基本上列数必须等于 GROUP BY 子句中的列数，但我仍然不明白为什么或发生了什么.

这就是 GROUP BY 的工作原理.它需要几行并将它们变成一行.因此，它必须知道如何处理某些列(字段)具有不同值的所有组合行.这就是为什么您对要 SELECT 的每个字段都有两个选项:要么将其包含在 GROUP BY 子句中，要么在聚合函数中使用它，以便系统知道您希望如何组合该字段.

例如，假设您有这张表:

名称 |订单号------------------约翰 |1约翰 |2

如果你说 GROUP BY Name，它怎么知道在结果中显示哪个 OrderNumber?所以你要么在 group by 中包含 OrderNumber，这将导致这两行.或者，您使用聚合函数来展示如何处理 OrderNumber.例如MAX(OrderNumber)，表示结果为John |2 或 SUM(OrderNumber) 表示结果是 John |3.

I am trying to understand GROUP BY (new to oracle dbms) without aggregate function.
How does it operate?
Here is what i have tried.

EMP table on which i will run my SQL.

SELECT ename , sal
FROM emp
GROUP BY ename , sal

SELECT ename , sal  
FROM emp  
GROUP BY ename;  

Result

ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action:
Error at Line: 397 Column: 16

SELECT ename , sal  
FROM emp  
GROUP BY sal;  

Result

ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action: Error at Line: 411 Column: 8

SELECT empno , ename , sal  
FROM emp  
GROUP BY sal , ename;  

Result

ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action: Error at Line: 425 Column: 8

SELECT empno , ename , sal  
FROM emp  
GROUP BY empno , ename , sal;  

So, basically the number of columns have to be equal to the number of columns in the GROUP BY clause, but i still do not understand why or what is going on.

That's how GROUP BY works. It takes several rows and turns them into one row. Because of this, it has to know what to do with all the combined rows where there have different values for some columns (fields). This is why you have two options for every field you want to SELECT : Either include it in the GROUP BY clause, or use it in an aggregate function so the system knows how you want to combine the field.

For example, let's say you have this table:

Name | OrderNumber
------------------
John | 1
John | 2

If you say GROUP BY Name, how will it know which OrderNumber to show in the result? So you either include OrderNumber in group by, which will result in these two rows. Or, you use an aggregate function to show how to handle the OrderNumbers. For example, MAX(OrderNumber), which means the result is John | 2 or SUM(OrderNumber) which means the result is John | 3.

这篇关于没有聚合函数的 GROUP BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！