如何在sql中解决查询？ [英] how to solve query in sql?

问题描述

对于使用以下命令创建的表。说明SQL SELECT命令，以查找在DEL或MUM中工作的员工所获得的第一和第二高工资

For a table created using the following commands. State the SQL SELECT command to find the 1st and 2nd highest salary earned by staff working in DEL or MUM

CREATE TABLE Employees (
    ID int NOT NULL AUTO_INCREMENT,
    Name varchar(100),
    Sal decimal (10,2),
    City char (3),
    PRIMARY KEY (ID)
);

INSERT INTO Employees (Name, Sal, City) VALUES ('Ramesh',20000, 'BLR');
INSERT INTO Employees (Name, Sal, City) VALUES ('Sunil',24000, 'DEL');
INSERT INTO Employees (Name, Sal, City) VALUES ('Sreeja',21000, 'MUM');
INSERT INTO Employees (Name, Sal, City) VALUES ('Pavan',23000, 'DEL');
INSERT INTO Employees (Name, Sal, City) VALUES ('Maya',24000, 'MUM');

推荐答案

我们不做你的作业：这是有原因的。它就是为了让你思考你被告知的事情，并试着理解它。它也在那里，以便您的导师可以识别您身体虚弱的区域，并将更多的注意力集中在补救措施上。



亲自尝试，你可能会发现它不是和你想的一样困难！



我会给你一个提示：SELECT有一个可选的TOP说明符，可以ORDER BY列......

We do not do your homework: it is set for a reason. It is there so that you think about what you have been told, and try to understand it. It is also there so that your tutor can identify areas where you are weak, and focus more attention on remedial action.

Try it yourself, you may find it is not as difficult as you think!

I'll give you a hint though: SELECT has an optional TOP specifier, and can ORDER BY columns...

我建​​议使用排名功能 [ ^ ]。



例如：

I suggest to use ranking functions[^].

For example:

SELECT RowNo, [Name], Sal
FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY City ORDER BY Sal DESC) AS RowNo, [Name], Sal
    FROM Employee
    WHERE City IN ('DEL','MUM') 
) AS T
WHERE RowNo IN (1,2)

注意：根据您的需要，您应该使用正确的排名功能。

NOTE: Depending on your needs, you should use proper ranking function.

忽略你得到的其他答案。一个是正​​确的，但过于复杂，你的老师会知道你被骗了，而另一个只是愚蠢和错误。阅读OriginalGriff的回答。使用基本SQL非常容易。



这里 [ ^ ]是关于select语句的文章。完成它。学习SQL并通过课程。不要试图欺骗并最终像你开始时一样无能为力。你找不到那份工作。

Ignore the other answers you got. One is correct, but over complicated and your teacher will know you cheated, and the other is just stupid and wrong. Read OriginalGriff's answer. It's really easy to do, with basic SQL.

Here[^] is my article on select statements. Work through it. Learn SQL and pass your course. Don't try to cheat and end up as clueless as you started. You won't find a job that way.

这篇关于如何在sql中解决查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！