记三个广大的ms,server中取第N条记下的章程

前言

好好学习,每一天向上。

前言

好好学习,每天向上。

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
上还有壹道选第第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

只是这么出来有一个标题,当第N大不设有时,重回的结果是个空集。而主题素材一般期望当未有值时,再次回到3个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

正文

类似也是一个便当的难题,刚录像里见到的,就记一下呢。

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

select * from Employee order by Salary desc

997755.com澳门葡京 1

先是来看一下怎样取Salary第3的记录。

--获取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第一的笔录

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

规律是先依据Salary降序排序获取到前三条记下,作为Result3个结果集

997755.com澳门葡京 3

然后再在这些结果集里面用Salary升序排序,取第三条。

997755.com澳门葡京 4

记三个广大的ms,server中取第N条记下的章程。上面再来看一下选择ROW_NUMBER;k(sql一叁.swb.tsqlresults.f一);k(sql13.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

结果也是1模一样的。

就到此地吧。

正文

就好像也是3个简易的标题,刚摄像里看到的,就记一下啊。

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

select * from Employee order by Salary desc

997755.com澳门葡京 7

首先来看一下怎么着取Salary第1的笔录。

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

997755.com澳门葡京 8

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

上边来看一下什么样取Salary第2的笔录

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

原理是先依照Salary降序排序获取到前3条记下,作为Result3个结实集

997755.com澳门葡京 9

接下来再在这些结果集里面用Salary升序排序,取第三条。

997755.com澳门葡京 10

上面再来看一下施用ROW_NUMBER;k(sql一三.swb.tsqlresults.f壹);k(sql一3.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澳门葡京 11

专注一下B和C的salary是同样的,然而得到的二个number值是见仁见智的,项目中看具体情形,选取必要的函数。

咱俩那边取RowNumber.

997755.com澳门葡京 12

结果也是千篇1律的。

就到此处吧。

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

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

997755.com澳门葡京 ,选拔降序排序以及对出口结果的限量求解。

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

行使自联结在1个表中举办查询四个一而再的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