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

  • 问题

  • 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呢?(这个问题是我朋友问我的,他要求表的索引不变

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


    2013年6月17日 4:23

答案

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

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

    • 已标记为答案 lynn磷 2013年6月18日 2:12
    2013年6月18日 1:14
  • 如果你的表就只有那么两个字段, 或者是确定不能做任何索引添加的话的话, 直接用 row_number 就好了

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

    谢谢!我也是这么想的:-)
    • 已标记为答案 lynn磷 2013年6月18日 2:12
    2013年6月18日 2:12

全部回复

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

    2013年6月17日 6:40
  • 如果有两个并列第一的,要求得到什么样的结果呀?
    2013年6月17日 7:29
    版主
  • 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表上作写法调整,真不知道该怎么写啊。。。。

    2013年6月17日 7:51
  • 如果有两个并列第一的,要求得到什么样的结果呀?
    那就一起出来呗,呵呵
    2013年6月17日 8:06
  • 如果你的表就只有那么两个字段, 或者是确定不能做任何索引添加的话的话, 直接用 row_number 就好了

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

    • 已标记为答案 lynn磷 2013年6月18日 2:12
    2013年6月18日 1:14
  • 如果你的表就只有那么两个字段, 或者是确定不能做任何索引添加的话的话, 直接用 row_number 就好了

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

    谢谢!我也是这么想的:-)
    • 已标记为答案 lynn磷 2013年6月18日 2:12
    2013年6月18日 2:12
  • 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表是实时变化的是吧,工资调整

    2013年6月18日 5:43