查询工资第二高的雇员的SQL,怎么写才能低消耗

Question

CREATE TABLE EmpSalaryInfo
(
empid VARCHAR(10) --雇员ID，建了主键
,salary money --工资

)

go

create clustered index idx_empid on EmpSalaryInfo(empid)

go

--查询工资第二高的雇员ID
SELECT empid
FROM
(
SELECT empid,ROW_NUMBER() OVER(ORDER BY salary DESC) RID
FROM EmpSalaryInfo
)P
WHERE RID=2

倘若这张表有上千万数据，该如何改写这个SQL呢？（这个问题是我朋友问我的，他要求表的索引不变）

请问大家有啥好的写法不？

Answer 1

SELECT TOP 1 *
FROM EmpSalaryInfo
WHERE salary < (SELECT MAX(salary) FROM EmpSalaryInfo)
ORDER BY salary DESC

Answer 2

如果有两个并列第一的，要求得到什么样的结果呀？

Answer 3

SELECT TOP 1 *
FROM EmpSalaryInfo
WHERE salary < (SELECT MAX(salary) FROM EmpSalaryInfo)
ORDER BY salary DESC

我比较了两者的逻辑读以及开启set statistics profile on 发现第二种写法的IO消耗是第一段的2倍，其实就是多了一次clustered index scan。

我在想：是否可以通过添加一个时间字段，每周（间隔时间根据业务来定）在空闲时间汇总该段时间内的最高工资至一张特定汇总表，然后根据汇总表查询第二高的工资。

如果在EmpSalaryInfo表上作写法调整，真不知道该怎么写啊。。。。

Answer 4

如果有两个并列第一的，要求得到什么样的结果呀？

那就一起出来呗，呵呵

Answer 5

如果你的表就只有那么两个字段， 或者是确定不能做任何索引添加的话的话， 直接用 row_number 就好了

否则的话，应该在 salary 上建立索引的

Answer 6

如果你的表就只有那么两个字段， 或者是确定不能做任何索引添加的话的话， 直接用 row_number 就好了

否则的话，应该在 salary 上建立索引的

谢谢！我也是这么想的：-）

Answer 7

SELECT TOP 1 *
FROM EmpSalaryInfo
WHERE salary < (SELECT MAX(salary) FROM EmpSalaryInfo)
ORDER BY salary DESC

我比较了两者的逻辑读以及开启set statistics profile on 发现第二种写法的IO消耗是第一段的2倍，其实就是多了一次clustered index scan。

我在想：是否可以通过添加一个时间字段，每周（间隔时间根据业务来定）在空闲时间汇总该段时间内的最高工资至一张特定汇总表，然后根据汇总表查询第二高的工资。

如果在EmpSalaryInfo表上作写法调整，真不知道该怎么写啊。。。。

子查询嘛，因为你这里涉及到排序，如果不在Salary上建立索引的话，查询不会明显加快

我觉得这里需要另外建立汇总表来归档，因为你这个EmpSalaryInfo表是实时变化的是吧，工资调整