server中取第N条记下的点子

前言

好好学习,每5日向上。

175.Combine Two Tables

Description

Write a SQL query to get the nth highest salary from the Employee
table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the nth highest salary
where n = 2 is 200. If there is no nth highest salary, then the query
should return null.

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

事实上 Leetcode
上还有1道选第第2大元素的难题,可是和那道题实质上一毛同样,所以就只记录那个难题了。
选第N大的要素思路已经很爽朗了,由于 sql
内置的函数不设有选第N大成分的函数,所以大家不得不另辟蹊径了,就依靠Salary排序排序,然后用offset选排序后的第N个成分,最终用LIMIT限制选择二个要素,正是第N大的万分成分了。
道理当然是那样的我的解法是这么的:

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  DECLARE M INT;
  SET M = N-1;
  RETURN (
      # Write your MySQL query statement below.
          SELECT DISTINCT Salary 
          FROM Employee
          ORDER BY Salary DESC LIMIT 1 OFFSET M
  );
END

唯独如此出来有1个主题材料,当第N大不存在时,重返的结果是个空集。而难点一般期望当未有值时,重临二个null,于是须求套个
SELECT ([expression]) AS Salary 那样的壳,所以最后结果是如此的:

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  DECLARE M INT;
  SET M = N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT (
          SELECT DISTINCT Salary 
          FROM Employee
          ORDER BY Salary DESC LIMIT 1 OFFSET M
      )
      AS getNthHighestSalary
  );
END

server中取第N条记下的点子。正文

接近也是1个简易的标题,刚摄像里观望的,就记一下吗。

上边是表中原始的数据结构,做了三个倒叙排序:

select * from Employee order by Salary desc

997755.com澳门葡京 1

率先来看一下哪些取Salary第2的笔录。

--获取salary排行第二的人的信息
select top 1 * from Employee where Salary < (select max(salary) from Employee ) order by Salary desc

997755.com澳门葡京 2

原理是先获得到最大的salary-maxSalary,然后依照salary降序排序,取第一条salary小于该maxSalary的记录.

上面来看一下哪些取Salary第3的记录

--获取salary排行第三的人的信息
select top 1 * from (
select top 3 * from Employee order by Salary desc
) as result
order by Salary asc

原理是先遵照Salary降序排序获取到前3条记下,作为Result三个结果集

997755.com澳门葡京 3

接下来再在那个结果集里面用Salary升序排序,取第一条。

997755.com澳门葡京 4

下边再来看一下施用ROW_NUMBER;k(sql壹3.swb.tsqlresults.f1);k(sql一三.swb.tsqlquery.f一);k(MiscellaneousFilesProject);k(DevLang-TSQL)&rd=true)(顺路试验了Rank,Dense_Rank那多少个函数)那个函数的写法:

--获取salary排行第三的人的信息
select * from (
select * ,row_number () over (order by salary desc) as RowNumber,RANK() over (order by salary desc) as RankNumber,DENSE_RANK() over (order by salary desc) as DenseRankNumber from Employee
) as Result
where Result.RowNumber =3

先看一下Result这么些函数的结果集:

997755.com澳门葡京 5

小心一下B和C的salary是一样的,可是得到的二个number值是见仁见智的,项目中看具体情状,选取供给的函数。

咱俩那边取RowNumber.

997755.com澳门葡京 6

结果也是如出一辙的。

就到此地呢。

Problem

Table: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+

PersonId is the primary key column for this table.
Table: Address

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+

AddressId is the primary key column for this table.
Write a SQL query for a report that provides the following information
for each person in the Person table, regardless if there is an address
for each of those people:
FirstName, LastName, City, State

Answer

行使外部联结,展现Person表中的所有行

select FirstName,LastName,City,State
from Person left join Address
on Person.PersonId=Address.PersonId;

176.Second Highest Salary

Problem

Write a SQL query to get the second highest salary from the Employee
table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the second highest salary
is 200.If there is no second highest salary, then the query should
return null.

Answer

利用子查询和过滤检索,排除掉最大的Salary

Select max(Salary) as SecondHighestSalary
from Employee
where Salary<(Select max(Salary) from Employee);

177.Nth Highest Salary

Problem

997755.com澳门葡京 ,Write a SQL query to get the nth highest salary from the Employee
table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the nth highest salary
where n = 2 is 200. If there is no nth highest salary, then the
query should return null.

Answer

接纳降序排序以及对出口结果的限制求解。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT ;
SET M=N-1;
RETURN(
Select Dinstict Salary from Employee
order by Salary desc
limit M,1
); 
END

178. Rank Scores

Problem

Write a SQL query to rank scores. If there is a tie between two
scores, both should have the same ranking. Note that after a tie, the
next ranking number should be the next consecutive integer value. In
other words, there should be no “holes” between ranks.

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

For example, given the above Scores table, your query should generate
the following report (order by highest score):

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

Answer

运用集中函数count()总计比本身分数高的作为rank

select Scores.Score,count(Ranking.Score) as Rank
from Scores,(Select distinct Score from Scores)Ranking
where Scores.Score<=Ranking.Scores 
group by Scores.Id
order by Scores.Score desc;

180. Consecutive Numbers

Problem

Write a SQL query to find all numbers that appear at least three times
consecutively.

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

For example, given the above Logs table, 1 is the only number that
appears consecutively for at least three times.

Answer

应用自联结在一个表中进行查询多个一而再的Num一样的Num。

Select distinct l1.Num as ConsecutiveNums
from Logs l1,Logs l2,Logs l3
where l1.Id=l2.Id-1 and l2.Id=l3.Id-1
and l1.Num=l2.Num and l3.Num=l2.Num;

181. Employees Earning More Than Their Managers

Problem

The Employee table holds all employees including their managers. Every
employee has an Id, and there is also a column for the manager Id.

+----+-------+--------+-----------+
| Id |  Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000 |      3     |
| 2  | Henry | 80000 |      4     |
| 3  | Sam   | 60000 |    NULL    |
| 4  | Max   | 90000 |    NULL    |
+----+-------+--------+-----------+

Given the Employee table, write a SQL query that finds out employees who
earn more than their managers. For the above table, Joe is the only
employee who earns more than his manager.

+----------+
| Employee |
+----------+
|   Joe    |
+----------+

Answer

照例利用自联结在三个表中举办查询

Select e1.Name as Employee
from Employee e1,Employee e2
where e1.ManagerId=e2.ManagerId and e1.Salary>e2.Salary;

182. Duplicate Emails

Problem

Write a SQL query to find all duplicate emails in a table named
Person.

+----+---------+
| Id | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+----+---------+

For example, your query should return the following for the above table:

+---------+
|  Email  |
+---------+
| a@b.com |
+---------+

Note: All emails are in lowercase.

Answer

依旧是利用自联结

select distinct p1.Email
from Person p1,Person p2
where p1.Email=p2.Email and p1.Id!=p2.Id;

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website