我试图在没有聚合函数的情况下理解 GROUP BY(oracle dbms 的新手)。它是如何运作的?这是我尝试过的。
https://i.stack.imgur.com/37oYU.jpg
SELECT ename , sal
FROM emp
GROUP BY ename , sal
https://i.stack.imgur.com/3n3JM.jpg
SELECT ename , sal
FROM emp
GROUP BY ename;
结果 ORA-00979:不是 GROUP BY 表达式 00979。00000 -“不是 GROUP BY 表达式” *原因:*操作:第 397 行错误,第 16 列
SELECT ename , sal
FROM emp
GROUP BY sal;
结果 ORA-00979:不是 GROUP BY 表达式 00979。00000 -“不是 GROUP BY 表达式” *原因:*操作:行错误:411 列:8
SELECT empno , ename , sal
FROM emp
GROUP BY sal , ename;
结果 ORA-00979:不是 GROUP BY 表达式 00979。00000 -“不是 GROUP BY 表达式” *原因:*操作:行错误:425 列:8
SELECT empno , ename , sal
FROM emp
GROUP BY empno , ename , sal;
https://i.stack.imgur.com/pP6BB.jpg
所以,基本上列数必须等于 GROUP BY 子句中的列数,但我仍然不明白为什么或发生了什么。
这就是 GROUP BY 的工作原理。它需要几行并将它们变成一行。因此,它必须知道如何处理某些列(字段)具有不同值的所有组合行。这就是为什么对于每个要 SELECT 的字段都有两个选项:要么将其包含在 GROUP BY 子句中,要么在聚合函数中使用它,以便系统知道您要如何组合该字段。
例如,假设您有这张表:
Name | OrderNumber
------------------
John | 1
John | 2
如果您说 GROUP BY Name,它如何知道要在结果中显示哪个 OrderNumber?因此,您可以在 group by 中包含 OrderNumber,这将导致这两行。或者,您使用聚合函数来展示如何处理 OrderNumber。例如,MAX(OrderNumber)
表示结果为 John | 2
或 SUM(OrderNumber)
表示结果为 John | 3
。
鉴于此数据:
Col1 Col2 Col3
A X 1
A Y 2
A Y 3
B X 0
B Y 3
B Z 1
这个查询:
SELECT Col1, Col2, Col3 FROM data GROUP BY Col1, Col2, Col3
将导致完全相同的表。
但是,此查询:
SELECT Col1, Col2 FROM data GROUP BY Col1, Col2
会导致:
Col1 Col2
A X
A Y
B X
B Y
B Z
现在,一个查询:
SELECT Col1, Col2, Col3 FROM data GROUP BY Col1, Col2
会产生一个问题:带有 A、Y 的行是对这两行进行分组的结果
A Y 2
A Y 3
那么,哪个值应该在 Col3、'2' 或 '3' 中?
通常您会使用 GROUP BY
来计算总和:
SELECT Col1, Col2, SUM(Col3) FROM data GROUP BY Col1, Col2
所以在这条线上,我们遇到了一个问题,我们现在得到 (2+3) = 5。
在选择中按所有列分组实际上与使用 DISTINCT 相同,在这种情况下,最好使用 DISTINCT 关键字词的可读性。
所以而不是
SELECT Col1, Col2, Col3 FROM data GROUP BY Col1, Col2, Col3
利用
SELECT DISTINCT Col1, Col2, Col3 FROM data
SELECT Col1, Col2, Col3 FROM data GROUP BY Col1
?
SELECT Col1, Col2, Col3 FROM data GROUP BY Col1, Col2
解释的相同问题,但有一个额外的问题列。对于 Col1
= A 的行,您希望 Col2
和 Col3
的值是多少?
group the data
A B A B
我想得到像这样的第三列结果的所有列:- A A B B
select * from table group by 3rd Column
会起作用吗?
您遇到了对 GROUP BY 子句的严格要求。不在 group-by 子句中的每一列都必须应用一个函数来将匹配“组”的所有记录减少为单个记录(sum、max、min 等)。
如果您在 GROUP BY 子句中列出所有查询(选择)的列,您实际上是在请求从结果集中排除重复记录。这提供了与 SELECT DISTINCT 相同的效果,它也消除了结果集中的重复行。
没有聚合的 GROUP BY 唯一真正的用例是当您 GROUP BY 的列多于选定的列时,在这种情况下,选定的列可能会重复。否则,您不妨使用 DISTINCT。
值得注意的是,其他 RDBMS 并不要求所有非聚合列都包含在 GROUP BY 中。例如,在 PostgreSQL 中,如果表的主键列包含在 GROUP BY 中,则该表的其他列不需要,因为它们保证对于每个不同的主键列都是不同的。过去,我希望 Oracle 能像在许多情况下为更紧凑的 SQL 所做的那样做同样的事情。
让我举几个例子。
考虑这些数据。
CREATE TABLE DATASET ( VAL1 CHAR ( 1 CHAR ),
VAL2 VARCHAR2 ( 10 CHAR ),
VAL3 NUMBER );
INSERT INTO
DATASET ( VAL1, VAL2, VAL3 )
VALUES
( 'b', 'b-details', 2 );
INSERT INTO
DATASET ( VAL1, VAL2, VAL3 )
VALUES
( 'a', 'a-details', 1 );
INSERT INTO
DATASET ( VAL1, VAL2, VAL3 )
VALUES
( 'c', 'c-details', 3 );
INSERT INTO
DATASET ( VAL1, VAL2, VAL3 )
VALUES
( 'a', 'dup', 4 );
INSERT INTO
DATASET ( VAL1, VAL2, VAL3 )
VALUES
( 'c', 'c-details', 5 );
COMMIT;
现在桌子上有什么
SELECT * FROM DATASET;
VAL1 VAL2 VAL3
---- ---------- ----------
b b-details 2
a a-details 1
c c-details 3
a dup 4
c c-details 5
5 rows selected.
-- 与分组聚合
SELECT
VAL1,
COUNT ( * )
FROM
DATASET A
GROUP BY
VAL1;
VAL1 COUNT(*)
---- ----------
b 1
a 2
c 2
3 rows selected.
--按多列分组但选择部分列
SELECT
VAL1,
COUNT ( * )
FROM
DATASET A
GROUP BY
VAL1,
VAL2;
VAL1
----
b
c
a
a
4 rows selected.
-- 没有按多列分组的聚合
SELECT
VAL1,
VAL2
FROM
DATASET A
GROUP BY
VAL1,
VAL2;
VAL1
----
b b-details
c c-details
a dup
a a-details
4 rows selected.
-- 没有按多列分组的聚合
SELECT
VAL1
FROM
DATASET A
GROUP BY
VAL1,
VAL2;
VAL1
----
b
c
a
a
4 rows selected.
您在选择中有 N 列(不包括聚合),那么您应该有 N 或 N+x 列
使用子查询,例如:
SELECT field1,field2,(SELECT distinct field3 FROM tbl2 WHERE criteria) AS field3
FROM tbl1 GROUP BY field1,field2
或者
SELECT DISTINCT field1,field2,(SELECT distinct field3 FROM tbl2 WHERE criteria) AS field3
FROM tbl1
如果 SELECT 子句中有一些列,如果有几行,它将如何选择它?所以是的,SELECT 子句中的每一列也应该在 GROUP BY 子句中,您可以在 SELECT 中使用聚合函数...
您可以在 GROUP BY 子句中拥有不在 SELECT 子句中的列,但不能在其他情况下
作为补充
基本上列数必须等于 GROUP BY 子句中的列数
不是正确的说法。
任何不属于 GROUP BY 子句的属性都不能用于选择
属于 GROUP BY 子句的任何属性都可以用于选择,但不是强制性的。
我知道你说过如果你有这样的数据你想了解 group by:
COL-A COL-B COL-C COL-D
1 Ac C1 D1
2 Bd C2 D2
3 Ba C1 D3
4 Ab C1 D4
5 C C2 D5
你想让数据看起来像:
COL-A COL-B COL-C COL-D
4 Ab C1 D4
1 Ac C1 D1
3 Ba C1 D3
2 Bd C2 D2
5 C C2 D5
你用:
select * from table_name
order by col-c,colb
因为我认为这是你打算做的。
不定期副业成功案例分享
GROUP BY
、ORDER BY
和聚合函数会发生什么。简单地说,克莱尔,举一个非常简单的例子。非常感谢!