在ms sql server查询中遇到Group By子句的问题 [英] Having problem with Group By clause in ms sql server query

问题描述

我已经查看了2个表 - Department Master和Employee Master。

这是表



Emp_Id Emp_Name Salary Dept_Id部门

1 Chandan 10000.00 1机械

2 Sudhir 11000.00 1机械

3 Rahul 20000.00 1机械

4 Kavish 15000.00 1机械

5 sapin 23000.00 2电脑

6 Kavita 23200.00 2电脑

7 amit 50000.00 2电脑





我希望获得部门名称和员工姓名的最高薪水

我通过查询使用此群组如下

I have made a view of 2 tables - Department Master and Employee Master.
This is the table

Emp_Id Emp_Name Salary Dept_Id Dept
1 Chandan 10000.00 1 Mechnical
2 Sudhir 11000.00 1 Mechnical
3 Rahul 20000.00 1 Mechnical
4 Kavish 15000.00 1 Mechnical
5 sapin 23000.00 2 Computer
6 Kavita 23200.00 2 Computer
7 amit 50000.00 2 Computer


I want to get Maximum salary with department name and Employee Name
I used this this group by query as follows

select MAX(Emp_Salery) as Emp_Sal from V_New_Emp_Master group by Dept_Id

我得到了最大的销售额，但是当我添加Emp_Name列时，我得到了一个错误如下所示

SQL查询

I am getting max salery , but when when I add Emp_Name column I get an error as followes
SQL query

select MAX(Emp_Salery),Emp_Name as Emp_Sal from V_New_Emp_Master group by Dept_Id

错误：

Error:

Msg 8120, Level 16, State 1, Line 1
Column 'V_New_Emp_Master.Emp_Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Is there any solution for this

Thanks

推荐答案

当你将数据分组在一起时，你只能返回小组条款和聚合信息，如计数或总和。

想一想：

When you group data together, you can only return the group clause and aggregate information such as a count or sum.
Think about it:

Name   PenColour  NoPens
Joe    Blue       2
Joe    Green      3
Mike   Blue       1
Mike   Red        4

当您按名称对这些组合时，您将得到合并记录：

When you GROUP these by name you get combined records:

Name   PenColour        NoPens
Joe    Blue and Green   2 and 3
Mike   Blue and Red     1 and 4

显然，你不能为Joe返回PenColour因为那里是两个不同的值，只有一行返回它们 - 而且SQL不知道它应该返回哪一个。因此，您可以返回组中其他信息的聚合，但不能返回多个值。



如果要查找具有最高工资的用户在每个组中，您需要使用更复杂的子查询。

请参阅此处 - 它解释得很好： http://www.xaprb.com/blog/2006/12/07/how-to-select-the- firstleastmax-row-per-group-in-sql / [ ^ ]

Clearly, you can't return a PenColour for Joe because there are two distinct values and only one row to return them in - And SQL deosn't know which one it should return. So you can return an aggregate of the other info in a group, but you can't return multiple values.

If you want to find the user with the maximum salary within each group, you need to use a more complex subquery.
See here - it explains it well: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/[^]

这篇关于在ms sql server查询中遇到Group By子句的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！