[SQL]3.26--175+176+177+178+180+181+182

175.组合两个表

题目

Code

SELECT FirstName, LastName, City, State

FROM Person LEFT JOIN Address  --由于需要Person表的所有信息，所以使用左联结

ON Person.PersonId = Address.PersonId;

on和where的区别：

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。 在使用left join时，on和where条件的区别如下：

1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

176. 第二高的薪水

题目

code

SELECT

(SELECT DISTINCT Salary

FROM Employee

ORDER BY Salary DESC

LIMIT 1 OFFSET 1) AS SecondHighestSalary;

要创建一个新表，存储可能只有一行的数据

LIMIT和LIMIT OFFSET的区别

select * from table limit 2,1;

//跳过2条取出1条数据，limit后面是从第2条开始读，读取1条信息，即读取第3条数据

select * from table limit 2 offset 1;

//从第1条（不包括）数据开始取出2条数据，limit后面跟的是2条数据，offset后面是从第1条开始读取，即读取第2,3条

177. 第N高的薪水

题目

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT

BEGIN

  SET n = N-1;

  RETURN (

      # Write your MySQL query statement below.

      SELECT DISTINCT Salary

      FROM Employee

      ORDER BY Salary DESC

      LIMIT n,1

  );

END

178. 分数排名

SELECT s1.Score, COUNT(DISTINCT(s2.score)) RANK

From Scores s1, Scores s2

WHERE s1.score <= s2.score

GROUP BY s1.Id

ORDER BY Rank;

窗口函数：

<窗口函数> OVER ([partition by <列清单>]

                        ORDER BY <排序用列清单>)

比如,按照产品类型(product_type)分类，在每个分类中按照价格(sale_price)排序，并将排序结果写入新列ranking中。

SELECT product_name, product_type, sale_price,

       RANK() OVER (PARTITION BY product_type

                                 ORDER BY sale_price) AS ranking

FROM Product;

180. 连续出现的数字

题目

代码

case when

SELECT DISTINCT Num AS ConsecutiveNums

FROM (

    SELECT Num,

    CASE

    WHEN @prev = Num then @count := @count+1

    WHEN (@prev := Num) is not null then @count := 1

    END AS CNT

    FROM Logs, (SELECT @prev := null, @count := null) as t

) AS temp

WHERE temp.CNT >= 3;

181. 超过经理收入的员工

方法一：创建两个虚拟表

代码

SELECT A.Name as Employee

FROM Employee A, Employee B

WHERE A.ManagerId = B.Id

AND A.Salary > B.Salary;

方法二：自联结

代码

SELECT A.Name AS Employee

FROM Employee A JOIN Employee B

ON A.ManagerId = B.Id

AND A.Salary > B.Salary;

182. 查找重复的电子邮箱

题目

Code

SELECT Email

FROM Person

GROUP BY Email

HAVING COUNT(Email) > 1;