甲骨文集团只有一列 [英] Oracle group by only ONE column

问题描述

我在Oracle数据库中有一个表，它有40列。
我知道如果我想通过查询来完成一个 group，那么select中的所有列必须在group by中。

我只是想做：

 从表格组中选择col1，col2，col3，col4，col5 by col3 
  

如果我尝试：



 从表格组中选择col1，col2，col3，col4，col5由col1，col2，col3，col4，col5 
  

它没有提供所需的输出。

我已经查过这个，但没有找到任何解决方案。我发现使用某种Add（）或count（*）函数的所有查询。

在Oracle中，不可能简单地按一列进行分组吗？

更新：

我的歉意，不够明确。

我的表：

  + -------- +  - -------- + ------------- + ------- + 
 | id | col1 | col2 | col3 | 
 + -------- + ---------- + ------------- + ------- + 
 | 1 | 1 |一些文字1 | 100 | 
 | 2 | 1 |一些文字1 | 200 | 
 | 3 | 2 |一些文字1 | 200 | 
 | 4 | 3 |一些文字1 | 78 | 
 | 5 | 4 |一些文字1 | 65 | 
 | 6 | 5 |一些文字1 | 101 | 
 | 7 | 5 |一些文字1 | 200 | 
 | 8 | 1 |一些文字1 | 200 | 
 | 9 | 6 |一些文字1 | 202 | 
 + -------- + ---------- + ------------- + ------- + 
  

并运行以下查询：

 从表中选择col1，col2，col3，其中col3 ='200'group by col1; 
  

我会得到以下所需的输出：

  + -------- + ---------- + ------------- + ---- --- + 
 | id | col1 | col2 | col3 | 
 + -------- + ---------- + ------------- + ------- + 
 | 2 | 1 |一些文字1 | 200 | 
 | 3 | 2 |一些文字1 | 200 | 
 | 7 | 5 |一些文字1 | 200 | 
 + -------- + ---------- + ------------- + ------- + 
  

解决方案

是的，你不能那样做。考虑一下...如果你有这样的表：

  Col1 Col2 Col3 
 AA 1 
 BA 2 
 CA 3 
  

你只用 Col2 ，它将分组到一行... Col1 和 Col3 ？这两者都有3个不同的行值。
您的DBMS应该如何显示这些内容？

  Col1 Col2 Col3 
 A？ A 1？ 
 B？ 2？ 
 C？ 3？ 
  

这就是为什么您必须按所有列进行分组，或者将它们聚合或连接起来。 （ SUM（）， MAX（）， MIN（）

向我们展示您希望结果如何，我相信我们可以帮助您。

编辑 - 回答：

首先，感谢您更新您的问题。你的查询没有 id ，但是你的预期结果是这样的，所以我会分别回答每一个。

strong>没有 id

您仍然需要按所有列进行分组，重新开始。

如果您通过以下方式运行没有任何分组的查询：

 从列表中选择col1，col2，col3 col3 ='200'
  

这回：

  + ---------- + ----------- -  + ------- + 
 | col1 | col2 | col3 | 
 + ---------- + ------------- + ------- + 
 | 1 |一些文字1 | 200 | 
 | 2 |一些文字1 | 200 | 
 | 5 |一些文字1 | 200 | 
 | 1 |一些文字1 | 200 | 
 + ---------- + ------------- + ------- + 
  

所以现在你只想看一次 col1 = 1 行。但是要做到这一点，您需要将所有列的所有列全部滚开，以便您的DBMS知道每个列的做法。如果您尝试仅按 col1 进行分组，则DBMS将发生错误，因为您没有告诉它如何处理 col2中的额外数据和 col3 ：

  select col1， col2，col3 from table where col3 ='200'group by col1 --Errors 
 
 + ---------- + ------------- + ------- + 
 | col1 | col2 | col3 | 
 + ---------- + ------------- + ------- + 
 | 1 |一些文字1 | 200 | 
 | 2 |一些文字1 | 200 | 
 | 5 |一些文字1 | 200 | 
 | ？ |一些文字1？| 200？ | 
 + ---------- + ------------- + ------- + 
  

如果按3进行分组，则DBMS知道将所有行组合在一起（这是您想要的），并且只会显示一次重复的行：

 从列表中选择col1，col2，col3其中col3 ='200'按col1，col2，col3分组
 
 + ---------- + ------------- + ------- + 
 | col1 | col2 | col3 | 
 + ---------- + ------------- + ------- + 
 | 1 |一些文字1 | 200 | 
 | 2 |一些文字1 | 200 | - 预期结果
 | 5 |一些文字1 | 200 | 
 + ---------- + ------------- + ------- + 
  

使用 id

如果你想看 id ，你必须告诉你的DBMS要显示哪个 id 。即使我们按所有列进行分组，您也不会得到您想要的结果，因为 id 列会使每行不同（它们不再分组在一起） p>

 从表中选择id，col1，col2，col3其中col3 ='200'按id，col1，col2，col3分组
 
 + -------- + ---------- + ------------- + ------- + 
 | id | col1 | col2 | col3 | 
 + -------- + ---------- + ------------- + ------- + 
 | 2 | 1 |一些文字1 | 200 | --id = 2 
 | 3 | 2 |一些文字1 | 200 | 
 | 7 | 5 |一些文字1 | 200 | 
 | 8 | 1 |一些文字1 | 200 | --id = 8 
 + -------- + ---------- + ------------- + ------ -  + 
  

因此，为了对这些行进行分组，我们需要明确说明如何处理 ID 秒。根据你想要的结果，你要选择 id = 2 ，这是最小值 id MIN（）：

  select MIN id），col1，col2，col3 from table where col3 ='200'group by col1，col2，col3 
 --Note，MIN（）是一个聚合函数，所以id不需要在$ b中$ b  

返回您想要的结果（ id ）：

  + -------- + ---------- + ----- -------- + ------- + 
 | id | col1 | col2 | col3 | 
 + -------- + ---------- + ------------- + ------- + 
 | 2 | 1 |一些文字1 | 200 | 
 | 3 | 2 |一些文字1 | 200 | 
 | 7 | 5 |一些文字1 | 200 | 
 + -------- + ---------- + ------------- + ------- + 
  

最后的想法

这是你的两个麻烦行：

  + -------- + --------- -  + ------------- + ------- + 
 | id | col1 | col2 | col3 | 
 + -------- + ---------- + ------------- + ------- + 
 | 2 | 1 |一些文字1 | 200 | 
 | 8 | 1 |一些文字1 | 200 | 
 + -------- + ---------- + ------------- + ------- + 
  

无论何时您碰到这些问题，只要想一想每一列需要做什么，一次一个。每当您进行分组或汇总时，您都需要处理所有列。

• id ，你只希望看到 id = 2 ，这是 MIN（）


• co1 ，您只希望看到不同的值，所以 GROUP BY


• col2 ，你只想看到不同的值，所以 GROUP BY


• col3 ，你只想看到不同的值，所以 GROUP BY

I have a table in Oracle database, which have 40 columns. I know that if I want to do a group by query, all the columns in select must be in group by.

I simply just want to do:

select col1, col2, col3, col4, col5 from table group by col3

If I try:

select col1, col2, col3, col4, col5 from table group by col1, col2, col3, col4, col5

It does not give the required output.

I have searched this, but did not find any solution. All the queries that I found using some kind of Add() or count(*) function.

In Oracle is it not possible to simply group by one column ?

UPDATE:

My apologies, for not being clear enough.

My Table:

+--------+----------+-------------+-------+
| id     | col1     | col2        | col3  |
+--------+----------+-------------+-------+
| 1      | 1        | some text 1 | 100   |
| 2      | 1        | some text 1 | 200   |
| 3      | 2        | some text 1 | 200   |
| 4      | 3        | some text 1 | 78    |
| 5      | 4        | some text 1 | 65    |
| 6      | 5        | some text 1 | 101   |
| 7      | 5        | some text 1 | 200   |
| 8      | 1        | some text 1 | 200   |
| 9      | 6        | some text 1 | 202   |
+--------+----------+-------------+-------+

and by running following query:

select col1, col2, col3 from table where col3='200' group by col1;

I will get the following desired Output:

+--------+----------+-------------+-------+
| id     | col1     | col2        | col3  |
+--------+----------+-------------+-------+
| 2      | 1        | some text 1 | 200   |
| 3      | 2        | some text 1 | 200   |
| 7      | 5        | some text 1 | 200   |
+--------+----------+-------------+-------+

解决方案

Long comment here;

Yeah, you can't do that. Think about it... If you have a table like so:

Col1 Col2 Col3
A    A    1
B    A    2
C    A    3

And you're grouping by only Col2, which will group down to a single row... what happens to Col1 and Col3? Both of those have 3 distinct row values. How is your DBMS supposed to display those?

Col1 Col2 Col3
A?   A    1?
B?        2?
C?        3?

This is why you have to group by all columns, or otherwise aggregate or concatenate them. (SUM(),MAX(), MIN(), etc..)

Show us how you want the results to look and I'm sure we can help you.

Edit - Answer:

First off, thanks for updating your question. Your query doesn't have id but your expected results do, so I will answer for each separately.

Without id

You will still need to group by all columns to achieve what you're going for. Let's walk through it.

If you run your query without any group by:

select col1, col2, col3 from table where col3='200'

You will get this back:

+----------+-------------+-------+
| col1     | col2        | col3  |
+----------+-------------+-------+
| 1        | some text 1 | 200   |
| 2        | some text 1 | 200   |
| 5        | some text 1 | 200   |
| 1        | some text 1 | 200   |
+----------+-------------+-------+

So now you want to only see the col1 = 1 row once. But to do so, you need to roll all of the columns up, so your DBMS knows what do to with each of them. If you try to group by only col1, you DBMS will through an error because you didn't tell it what to do with the extra data in col2 and col3:

select col1, col2, col3 from table where col3='200' group by col1 --Errors

+----------+-------------+-------+
| col1     | col2        | col3  |
+----------+-------------+-------+
| 1        | some text 1 | 200   |
| 2        | some text 1 | 200   |
| 5        | some text 1 | 200   |
| ?        | some text 1?| 200?  |
+----------+-------------+-------+

If you group by all 3, your DBMS knows to group together the entire rows (which is what you want), and will only display duplicate rows once:

select col1, col2, col3 from table where col3='200' group by col1, col2, col3

+----------+-------------+-------+
| col1     | col2        | col3  |
+----------+-------------+-------+
| 1        | some text 1 | 200   |
| 2        | some text 1 | 200   | --Desired results
| 5        | some text 1 | 200   |
+----------+-------------+-------+

With id

If you want to see id, you will have to tell your DBMS which id to display. Even if we group by all columns, you won't get your desired results, because the id column will make each row distinct (They will no longer group together):

select id, col1, col2, col3 from table where col3='200' group by id, col1, col2, col3

+--------+----------+-------------+-------+
| id     | col1     | col2        | col3  |
+--------+----------+-------------+-------+
| 2      | 1        | some text 1 | 200   | --id = 2
| 3      | 2        | some text 1 | 200   |
| 7      | 5        | some text 1 | 200   |
| 8      | 1        | some text 1 | 200   | --id = 8
+--------+----------+-------------+-------+

So in order to group these rows, we need to explicitly say what to do with the ids. Based on your desired results, you want to choose id = 2, which is the minimum id, so let's use MIN():

select MIN(id), col1, col2, col3 from table where col3='200' group by col1, col2, col3
--Note, MIN() is an aggregate function, so id need not be in the group by

Which returns your desired results (with id):

+--------+----------+-------------+-------+
| id     | col1     | col2        | col3  |
+--------+----------+-------------+-------+
| 2      | 1        | some text 1 | 200   |
| 3      | 2        | some text 1 | 200   |
| 7      | 5        | some text 1 | 200   |
+--------+----------+-------------+-------+

Final thought

Here were your two trouble rows:

+--------+----------+-------------+-------+
| id     | col1     | col2        | col3  |
+--------+----------+-------------+-------+
| 2      | 1        | some text 1 | 200   |
| 8      | 1        | some text 1 | 200   |
+--------+----------+-------------+-------+

Any time you hit these, just think about what you want each column to do, one at a time. You will need to handle all columns any time you do grouping or aggregates.

• id, you only want to see id = 2, which is the MIN()
• co1, you only want to see distinct values, so GROUP BY
• col2, you only want to see distinct values, so GROUP BY
• col3, you only want to see distinct values, so GROUP BY

这篇关于甲骨文集团只有一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！