sql查询找到八个工资 [英] Sql query find eight salary

问题描述

如何从下表中找到八个高度的工资我们有两个表Employee_Table和工资表

 Employee_Table 
字段姓名
 ID姓名指定
 1 ab cd 
 2 bc ce 
 3 ef fd 
 4 vc fd 
 5 vd bv 
 6 df de 
 7 fg rt 
 8 gf tg 

 Salary_Table 
字段名称
 SalID Salary EmpID 
 1 1200 1 
 2 1255 2 
 3 2500 3 
 4 2600 4 
 5 2100 6 
 6 2500 5 
 7 3000 8 
 8 4000 7 

已添加代码块 - OriginalGriff [/ edit]

解决方案

试试这个：

  SELECT   TOP   1 薪水
  FROM （
  SELECT   DISTINCT   TOP   8  
 e.Name，
 e.Designation，
 s.Salary 
  FROM  Employee_Table e 
 < span class =code-keyword> JOIN  Salary_Table s 
  ON  e.ID = s.EmpID 
  ORDER   BY  s.Salary  DESC ）a 
  ORDER   BY 薪资

查找这里的解决方案

找到第N个最高的使用RANKING函数的alary

尝试：

  SELECT   TOP   8  e.Name，e.Designation，s.Salary  FROM  Employee_Table e 
  JOIN  Salary_Table s 
  ON  e.ID = s.EmpID 
  ORDER   BY  s.Salary 

不要尝试它那个 - 你想从一个JOIN开始强制将两个表放在相关的数据上 - 员工ID：

然后，你需要订购你的行，并提供一个行号 - 否则它不是一个最高的值！



试试这个：

 < span class =code-keyword> SELECT 名称，名称，薪水 FROM  
（ SELECT  e.Name，e.Designation，s.Salary，ROW_NUMBER（） OVER （ ORDER   BY  s.Salary  DESC ） AS  R  FROM  Employee_Table e 
  JOIN  Salary_Table s 
  ON  e.ID = s.EmpID） AS  A 
  WHERE  AR = 3 

这很简单，如果你看一下这些位：

  SELECT  e.Name，e.Designation，s.Salary，ROW_NUMBER（） OVER （ ORDER   BY  s.Salary  DESC ） AS  R  FROM  Emplo yee_Table e 
  JOIN  Salary_Table s 
  ON  e.ID = s.EmpID 

所有这一切都说两个表是通过Employee ID链接在一起的，并且我希望按薪水排序的每行的行号最高。 e和s位只是证明了表的较短名称，所以我不必一直输入Employee_Table和Salary_Table！



外部SELECT然后指定您想要返回的内容 - 仅第三行，以及该行的相关信息。

how to find eight heights salary from following table we have two table Employee_Table and salary table

Employee_Table                 
field name                      
ID Name designation                
1  ab     cd                                    
2  bc     ce                        
3  ef     fd                        
4  vc     fd                       
5  vd     bv                      
6  df     de                        
7  fg     rt                       
8  gf     tg                       

Salary_Table
 field name
SalID Salary EmpID 
1     1200   1   
2     1255   2
3     2500   3
4     2600   4
5     2100   6
6     2500   5 
7     3000   8
8     4000   7

[edit]Code block added - OriginalGriff[/edit]

解决方案

Try this:

SELECT TOP 1 Salary
FROM (
SELECT DISTINCT TOP 8 
e.Name, 
e.Designation, 
s.Salary 
FROM Employee_Table e
JOIN Salary_Table s
ON e.ID = s.EmpID
ORDER BY s.Salary DESC) a
ORDER BY Salary

Find the solution here
Find Nth highest salary using RANKING functions

Try:

SELECT TOP 8 e.Name, e.Designation, s.Salary FROM Employee_Table e
JOIN Salary_Table s
ON e.ID = s.EmpID
ORDER BY s.Salary

[EDIT]
Don't try it quite like that - you want to start with a JOIN to force the two tables together on the relevant data - the Employee ID:
Then, you need to order your rows, and provide a row number - otherwise it's not a "highest" value!

Try this:

SELECT Name, Designation, Salary FROM
    (SELECT e.Name, e.Designation, s.Salary , ROW_NUMBER() OVER (ORDER BY s.Salary DESC) AS R FROM Employee_Table e
    JOIN Salary_Table s
    ON e.ID = s.EmpID) AS A
WHERE A.R=3

It's pretty simple, if you look at the bits:

SELECT e.Name, e.Designation, s.Salary , ROW_NUMBER() OVER (ORDER BY s.Salary DESC) AS R FROM Employee_Table e
JOIN Salary_Table s
ON e.ID = s.EmpID

All this does is say that the two tables are linked together by the Employee ID, and that I want the row number of each row ordered by salary, highest first. The "e" and "s" bits just proved shorter names for the tables, so I don't have to type Employee_Table and Salary_Table all the time!

The outer SELECT then specifies exactly what you want to return - the Third row only, and the relevant info from that row.

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