MySQL：选择前 n 个最大值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19827388/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: Select top n max values?
提问by Hoa Vu
I am really confused about the query that needing to return top N rows having biggest values on particular column.
我对需要返回在特定列上具有最大值的前 N 行的查询感到非常困惑。
For example, if the rows N-1, N, N + 1have same values. Must I return just top Nor top N + 1rows.
例如，如果行N-1, N, N + 1具有相同的值。我必须返回just top N或top N + 1行。
回答by Gordon Linoff
If you do:
如果你这样做：
select *
from t
order by value desc
limit N
You will get the top N rows.
您将获得前 N 行。
If you do:
如果你这样做：
select *
from t join
(select min(value) as cutoff
from (select value
from t
order by value
limit N
) tlim
) tlim
on t.value >= tlim;
Or you could phrase this a bit more simply as:
或者您可以更简单地将其表述为：
select *
from t join
(select value
from t
order by value
limit N
) tlim
on t.value = tlim.value;
The following is conceptually what you want to do, but it might not work in MySQL:
以下是您在概念上想要执行的操作，但它可能不适用于 MySQL：
select *
from t
where t.value >= ANY (select value from t order by value limit N)
回答by Tanmay S Mandalay
Use the following SQL query.
使用以下 SQL 查询。
SELECT salary FROM salesperson
ORDER BY salary DESC
LIMIT 2,1
回答by Shirishkumar Bari
You should use self join for this.
您应该为此使用自联接。
- first find the top (n) possible values for a perticular column
- join it with same table based on the primary key
- 首先找到特定列的前 (n) 个可能值
- 根据主键将其与同一张表连接
For E.g. on below sample table
例如下面的示例表
CREATE TABLE `employee` (
`ID` INT(11) AUTO_INCREMENT PRIMARY KEY,
`NAME` VARCHAR(50) NOT NULL,
`SALARY` INT(11) NOT NULL ,
JOINING_DATE TIMESTAMP
) ENGINE=MYISAM
INSERT INTO employee (NAME,salary,joining_date) VALUES('JAMES',50000,'2010-02-02'),
('GARGI',60000,'2010-02-02'),('DAN',30000,'2010-02-02'),('JOHN',10000,'2010-02-02'),('MICHEL',70000,'2010-02-02'),
('STIEVE',50000,'2010-02-02'),('CALRK',20000,'2010-02-02'),('BINNY',50000,'2010-02-02'),('SMITH',40000,'2010-02-02'),
('ROBIN',60000,'2010-02-02'),('CRIS',80000,'2010-02-02');
With the above table-data set up Query to find employees having top 3 salarieswould be :
使用上面的表数据设置查询以查找工资最高的员工将是：
SELECT e1.* FROM
(SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 3 ) S1
JOIN employee e1
ON e1.salary = s1.salary
ORDER BY e1.salary DESC
TIP:-
提示：-
If you need top 4 then just change LIMIT 3to LIMIT 4
如果您需要前 4 名，则只需更改LIMIT 3为LIMIT 4
