查找最低和最低工资总额 [英] Find miximum and minimum gross salary

问题描述

如何找出部门明智的每个指定最大GS和最低GS与empid。



我尝试过：

How can find-out Department wise Every Designation maximum GS And Minimum GS with empid.

What I have tried:

select A.EmpID,A.EmpName,B.DeptName,A.DeptId,D.DesigName,A.DesigID, GS from tblEmp_Info AS A
inner join tblCat_Department AS B ON A.DeptId=B.DeptID 
inner join tblCat_Desig AS D ON A.DesigId=D.DesigId
where IsInactive=0 and A.DeptId=4
order by A.deptid,A.desigid,GS desc

这是我的数据库代码，这里是show所有gs（工资总额）最多到最小。但我只想要部门明智的每个指定miximum和minimun GS与empid。请尽快帮助我。

推荐答案

检查一下： sql - 每个部门的最高工资 - Stack Overflow [ ^ ]



您必须根据需要进行更改：

Check this: sql - Highest Salary in each department - Stack Overflow[^]

You have to change it to your needs:

WITH cteRowNum AS (
    SELECT DeptID, EmpName, Salary,
           DENSE_RANK() OVER(PARTITION BY DeptID ORDER BY Salary DESC) AS RowNum
        FROM EmpDetails
)
SELECT DeptID, EmpName, Salary
    FROM cteRowNum
    WHERE RowNum = 1;

在您要检索的所选列上使用具有group by子句的聚合函数。



选择B.DeptName，A.DeptId，D.DesigName ，A.DesigID，max（GS）为MaxSal，min（GS）为来自tblEmp_Info的MinSal AS A

内部联接tblCat_Department AS B ON A.DeptId = B.DeptID

inner join tblCat_Desig AS D ON A.DesigId = D.DesigId

其中IsInactive = 0且A.DeptId = 4

group by B.DeptName，A.DeptId， D.DesigName，A.DesigID，GS

由A.deptid，A.desigid，GS desc命令

Use aggregate function with group by clause on the selected columns on which u want to retrieve.

select B.DeptName,A.DeptId,D.DesigName,A.DesigID, max(GS) as MaxSal,min(GS) as MinSal from tblEmp_Info AS A
inner join tblCat_Department AS B ON A.DeptId=B.DeptID
inner join tblCat_Desig AS D ON A.DesigId=D.DesigId
where IsInactive=0 and A.DeptId=4
group by B.DeptName,A.DeptId,D.DesigName,A.DesigID,GS
order by A.deptid,A.desigid,GS desc

这篇关于查找最低和最低工资总额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！