我怎样才能获得最高薪水 [英] How do i get maximum salary full row

问题描述

我需要获得MAX SALARY HOLDER NAME，ID ,,,这就是完整的行。



我尝试了什么：



SELECT * FROM AVI_02Mar2016 WHERE SALARY =（从AVI_02Mar2016中选择最大值（SALARY））

I NEED TO GET MAX SALARY HOLDER NAME, ID ,,,,THAT FULL ROW.

What I have tried:

SELECT * FROM AVI_02Mar2016 WHERE SALARY = (SELECT Max(SALARY ) FROM AVI_02Mar2016)

推荐答案

并且只是对于它的地狱还有另一种变化，没有任何这种新奇的CTE和ROWNUMBER的东西。它与原版没有什么不同，它使用派生表而不是子选择。



这是什么（以及海报的子选择版本）这样做ROWNUMBER和CTE解决方案没有（似乎）做的是返回 所有 员工的最高工资而不仅仅是第一个。你永远不知道，销售团队可能会有两个或两个以上非常热衷的人。 :)

And just for the hell of it yet another variation without any of this new-fangled CTE and ROWNUMBER stuff. It's not very different from the original, it uses a derived table rather than a sub-select instead.

What this (and the poster's sub-select version) do that the ROWNUMBER and CTE solutions don't (appear) to do is return all employees on the maximum salary not just the first one. You never know, there might be two or more very keen bods on the sales team. :)

select * from avi_ddMMMyy
inner join (select max(salary) as salary from
            avi_ddMMMyy) as maxima
on maxima.salary = avi_ddMMMyy.salary

你的查询工作原理。



更简单的是

Your query works as it is.

Simpler would be

SELECT TOP 1 * FROM AVI_02Mar2016 ORDER BY SALARY DESC

如果你想从一组表中获得最高工资然后你可以使用CTE

If you are trying to get the maximum salary from a set of tables then you can use a CTE

;WITH AllTables AS
(
	SELECT TOP 1 * FROM AVI_02Mar2016 ORDER BY SALARY DESC
	UNION ALL
	SELECT TOP 1 * FROM AVI_02Apr2016 ORDER BY SALARY DESC
	-- UNION ALL ... all of your other tables
)
SELECT TOP 1 * FROM AllTables ORDER BY SALARY DESC

更好的方法是拥有一个表 AVI 并为该日期添加一个额外的列。

Even better would be to have a single table AVI and have an extra column for the date.

你必须使用 ROW_NUMBER（）排名功能。

You have to use ROW_NUMBER() ranking function.

SELECT t.*
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS rn
    FROM YourTable
) AS t
WHERE t.rn = 1

如果您想获得有关排名功能的更多信息，请参阅：< a href =https://msdn.microsoft.com/en-us/library/ms189798%28v=sql.110%29.aspx>排名函数（Transact-SQL） [ ^ ]

这篇关于我怎样才能获得最高薪水的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！