从具有三个颜色Id、名称、薪资的表中查找第二高薪资

Question

如何从包含三列的表中找到第二高薪资，这些列是id，name，salary，但通过nested query.在SELF JOIN.Got中使用但是，我想知道我们如何使用SELF JOIN

Answer 1

如果只能使用SELECT语句，为什么要加入？

尝试如下：

SELECT DISTINCT salary FROM myTable ORDER BY salary DESC LIMIT 1,1 ;

Answer 2

如果你必须使用自我连接，你可以这样做.

SELECT x.val 
  FROM my_table x 
  JOIN my_table y 
    ON y.val >= x.val 
 GROUP 
    BY x.val HAVING COUNT(DISTINCT y.val) = ?

Answer 3

从具有三个颜色Id、名称、薪资的表中查找第二高的薪资：

SELECT id,NAME,salary FROM high
WHERE salary = (SELECT DISTINCT(salary) FROM high AS e1
WHERE (SELECT COUNT(DISTINCT(salary))=2 FROM high AS e2
WHERE e1.salary <=e2.salary))
ORDER BY NAME;

看 http://sqlfiddle.com/#!2/99a14/1

anthor一个有趣的答案：

SELECT id,NAME,salary
FROM high 
WHERE salary = (SELECT DISTINCT(salary) 
                FROM high AS e1 
                WHERE id  = (SELECT COUNT(DISTINCT(salary)) 
                             FROM high AS e2 
                             WHERE e1.salary <= e2.salary))