oracle 如何获取所有列，但必须在 sql 查询中仅按 2 列分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15784760/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get all the columns, but have to group by only 2 columns in sql query
提问by Muthukumar
I have a table Employees, which has Fields as below:
我有一个表Employees，其中的字段如下：
Employee_name,Employee_id,Employee_status,Employee_loc,last_update_time.
This table does not have any constraint. I have tried the below query.
该表没有任何约束。我试过下面的查询。
select Employee_name, count(1)
from Employees
where Employee_status = 'ACTIVE'
Group by Employee_name,Employee_loc
having count(Employee_name) > 1
order by count(Employee_name) desc
In the select, I need to get Employee_idtoo.. Can any one help on how to get that?
在选择中，我也需要得到Employee_id.. 任何人都可以帮助如何得到它？
回答by
You can just add Employee_idto the query, and also add it to the group byclause. (Adding it to the grouping won't make any difference in the query results, assuming each employee name each employee id is unique).
您可以只添加Employee_id到查询中，也可以将其添加到group by子句中。（将其添加到分组中不会对查询结果产生任何影响，假设每个员工姓名每个员工 ID 都是唯一的）。
Ifthe grouping does make a difference, that implies that some combinations of employee name and location have more than one ID associated with them. Your query would therefore need to decide which ID to return, possibly by using an aggregate function.
如果分组确实有所不同，则意味着员工姓名和位置的某些组合具有多个关联的 ID。因此，您的查询需要决定返回哪个 ID，可能是通过使用聚合函数。
回答by Santhosh
SELECT EMPLOYEE_NAME, EMPLOYEE_ID, COUNT(1)
FROM
EMPLOYEES
WHERE
EMPLOYEE_NAME IN
(
SELECT EMPLOYEE_NAME
FROM EMPLOYEES
WHERE Employee_status = 'ACTIVE'
GROUP BY Employee_name,Employee_loc
HAVING COUNT(*) > 1
)
GROUP BY EMPLOYEE_NAME, EMPLOYEE_ID
回答by KLeonine
You can also use partition by clause and select whichever columns you want to see irrespective of the columns you are using for aggregation.
您还可以使用 partition by 子句并选择您想要查看的任何列，而不管您用于聚合的列。
A very short and simple explanation here - Oracle "Partition By" Keyword
这里有一个非常简短的解释 - Oracle“Partition By”关键字
