数据库中的第n个身高工资 [英] nth height salary from database

问题描述

我对从表中检索第n个身高工资有疑问，我写了查询其工作f9

我写了下面显示的代码，但仍有效，但我怀疑为什么我们最后使用了

hi i have some doubt for retrieving nth height salary from table i write query its working f9

i wrote code shown below it''s working but i have one doubt why we are using at last

a
ORDER BY salary

告诉我plzzzzzzzzzzzzzz


选择前1名工资
从(
选择DISTINCT前5名薪水
来自员工
按工资DESC排序)a
ORDER BY Salary

in ''a'' tell me plzzzzzzzzzzzz


SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 5 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

推荐答案

CREATE TABLE Employee
(
Id bigint identity not null primary key,
Name nvarchar(255) not null,
Salary DECIMAL not null
)
go

INSERT INTO Employee(Name,Salary) VALUES(''Emp1'',100001)
go
INSERT INTO Employee(Name,Salary) VALUES(''Emp2'',100002)
go
INSERT INTO Employee(Name,Salary) VALUES(''Emp3'',100003)
go
INSERT INTO Employee(Name,Salary) VALUES(''Emp4'',100004)
go
INSERT INTO Employee(Name,Salary) VALUES(''Emp5'',100005)
go
INSERT INTO Employee(Name,Salary) VALUES(''Emp5-1'',100005)
go
INSERT INTO Employee(Name,Salary) VALUES(''Emp6'',100006)
go
INSERT INTO Employee(Name,Salary) VALUES(''Emp6-1'',100006)
go
INSERT INTO Employee(Name,Salary) VALUES(''Emp7'',100007)
go

执行

Executing

SELECT TOP 1 salary
 FROM (
  SELECT DISTINCT TOP 5 salary
  FROM employee
  ORDER BY salary DESC) a
ORDER BY salary

返回期望的薪水100003-第五高的薪水.

删除"a"会给我们带来错误:关键字"ORDER"附近的语法不正确，因为SQL解析器a需要用它作为内部选择结果的占位符.

http://www.4guysfromrolla.com/webtech/010406-1.shtml [ ^ ]

MS SQL Server支持标准的快速变体 [
大多数事情本质上都是暂时的，您的数据库设计应该能够处理此问题而不会丢失/覆盖有价值的信息.


问候
Espen Harlinn

returns the much hoped for salary 100003 - the 5th highest salary.

Removing the ''a'' gives us the error: Incorrect syntax near the keyword ''ORDER'' since the it''s needed by the SQL parser a as a placeholder for the result of the inner select.

http://www.4guysfromrolla.com/webtech/010406-1.shtml[^]

MS SQL Server supports the standard fast variant[^]

Apart from that, putting the salary in the employee table strikes me as a strange design - or is it a temporary table?

I would rather have devided the design into several tables - something like:
Person (Id integer NOT NULL PRIMARY KEY, ...)
Position( Id integer NOT NULL PRIMARY KEY, ...)
Wages(Id integer NOT NULL PRIMARY KEY, Salary DECIMAL ,...)

Employment(Id integer NOT NULL PRIMARY KEY,PersonId integer, PositionId integer, DateTime2 NOT NULL, ThroughTime DateTime2,...)

PositionWage(Id integer NOT NULL PRIMARY KEY, PositionId Integer, WageId integer,FromTime DateTime2 NOT NULL, ThroughTime DateTime2, .... )

This will allow:
a person to hold multiple positions - change jobs.

a position to be filled by multiple employees - and
reflect changes over time - the position stays even
if it''s filled by a different person

allow wages to change over time

A "real" solotion needs to be quite a bit more complicated to deal with the "real world" - My point is that having salary as a member of employee will cause your system to loose valuable information over time.

Most things are temporal in nature, your database design should be able to deal with this without loosing/overwriting valuable information.


Regards
Espen Harlinn

为确保正确无误，应为:

To be sure and correct, it should be:

SELECT TOP 1 salary
FROM (
       SELECT DISTINCT TOP 5 salary
       FROM employee
     ) a
ORDER BY salary DESC

或

OR

SELECT TOP 1 salary
FROM (
       SELECT DISTINCT TOP 5 salary
       FROM employee
       ORDER BY salary DESC
      ) a

可以将"a"视为内部查询形成的结果集的别名/临时名称.

举个例子，自己看看.

''a'' can be considered as a alias/temporary name given to the resultset formed from the inner query.

Take an example and see for yourself.

这篇关于数据库中的第n个身高工资的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！