我相信这个采访问题正试图引导您嵌套选择, 或通用表格表达式, 或类似的东西。 TOP 2 是一个简单的答案, 显然, TOP 是为了这个目的实施的, 访问希望您“ 手动” 。

在理论代码中。 在第一个( 取消的) 选中的行中给每行一行计数, 然后从结果中选择行计数少于您需要的行数的一多一的行数, 在此情况下选择 3 。

< a href=" "https://stackoverflow.com/ questions/2520357/mysql-get-row-row-number- on-select" >MySQL - 在选择 上获取行号

括号选择( 假代号) :

select row_count, * from salary order by salary desc

外部选择 :

select * from <nested select> where row_count < 3

我很抱歉这不是 MySQL 代码, 但我只知道 SQL 服务器 。

我有一些 SQL 服务器代码 使用行数来工作 :

declare @Salaries table
(
   id int,
   salary money
)

insert into @salaries (id, salary)
values (1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 4)  -- A duplicating salary

;WITH Props AS
(
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY salary desc) AS RowNumber
    FROM @Salaries
)
SELECT * FROM Props WHERE RowNumber < 3

该返回带有ID 4和ID 5的行。

< 强势 > 打字 Sachin Kainth 回答 < 强/ 强 >

我认为这个答案不正确。 请尝试以下 SQL 服务器代码 :

declare @Salaries table
(
   id int,
   salary money
)

insert into @salaries (id, salary)
values (1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 4)

select * from @salaries where salary in -- "in" introduces the problem
(
SELECT MAX(E1.Salary)  
FROM @salaries E1, @salaries E2
WHERE E1.Salary < E2.Salary

union

SELECT MAX(Salary)  
FROM @salaries
)

这返回了带有ID3、4和5的行,而不是仅仅4和5。 这是因为外选加上 < /code> 中工资的 < /code> 条款的 < code> 将包含第3、4和5行,这些行的工资都由嵌套选择返回(它返回工资值3和4)。

根据 SQL: 2008 标准, 您可以在您的查询中先附加 < code> FETCH 1 10 ROWS 。 虽然, 我从未尝试过这一点。 因此, 在您的情况中, 您本来会尝试过

SELECT * FROM salary ORDER BY salary DESC FETCH FIRST 2 ROWS ONLY

这是在 MySQL 中:

SET @row := 0;
SELECT name, salary FROM
(SELECT name, salary, @row := @row + 1 AS Row FROM salary ORDER BY salary DESC)
  AS derived1
WHERE Row < 3

如果工资有重复,结果可能会被扭曲。如果结果超过两行,则不将铁条包括在结果中。然而,由于问题是两名工资最高的雇员,而不是两名工资最高的雇员,所以这是我所能做的最好的事情。

也许正确的答案是问:“如果工资重复,我该怎么办?”

如果绝对是单一个查询的话,这里的诀窍是:

SELECT name, salary FROM
(SELECT name, salary, @row := @row + 1 AS Row FROM (SELECT @row := 0) AS d1, salary)
  AS d2
WHERE Row < 3

select * from salary where salary in
(
SELECT MAX(E1.Salary)  
FROM Salary E1, Salary E2
WHERE E1.Salary < E2.Salary

union

SELECT MAX(Salary)  
FROM Salary
)

+------+
| Sal  |
+------+
| 3500 | 
| 2500 |
| 2500 | 
| 5500 |
| 7500 |
+------+

以下查询将返回 Nth 最大元素 。

select SAL from EMPLOYEE E1 where 
 (N - 1) = (select count(distinct(SAL)) 
            from EMPLOYEE E2 
            where E2.SAL > E1.SAL )

表格表格结构结构

CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `salary` int(10) DEFAULT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

Mysql 查询 nth 术语 n 代表项目 nth 编号

SELECT salary 
FROM emp 
WHERE salary = (SELECT DISTINCT(salary) 
                FROM emp AS e1 
                WHERE (n) = (SELECT COUNT(DISTINCT(salary)) 
                             FROM emp AS e2 
                             WHERE e1.salary <= e2.salary))

SELECT e1.EmployeeID, e1.LastName, COUNT(DISTINCT e2.EmployeeID) AS sals_higher
FROM Employees e1
INNER JOIN Employees e2 ON e1.EmployeeID < e2.EmployeeID
GROUP BY e1.EmployeeID
HAVING sals_higher <= 2
ORDER BY e1.EmployeeID DESC

Visit https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_union3 and try the given piece of code, it gives you top 2 max EmployeeID.

我觉得这也许行得通

    select * from salary s1 where 2>=(select count(distinct id) from salary s2
 where s1.salary<=s2.salary) order by salary desc;

This is co-related query.For every row in outer query,inner query will run and will return a count value of by comparing salary value of from outer query to every salary in table . It is like a nested loop of programming language

说我们有两列工资和两列工资

id Salary
1  1200
2  12345
3  123456
4  2535436 

产出将是

id salary
4  2535436
2  123456