22.6.16 sql和shell练习

Sql

175. 组合两个表

select firstName,lastName,city,state from Person p
left join Address a
on p.personId = a.personId

176. 第二高的薪水
知识点：临时表，输出null；offset N

select (select distinct salary from Employee order by salary desc limit 1 offset 1) as SecondHighestSalary

177. 第N高的薪水
函数练习

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N = N - 1;
    Set @c=null ;
    SELECT DISTINCT salary into @c FROM employee ORDER BY salary DESC LIMIT N, 1;
  RETURN (
      # Write your MySQL query statement below.
      @c
  );
END

178. 分数排名

select Score, dense_rank() over(Order By Score desc) 'Rank' FROM Scores;

180. 连续出现的数字

select distinct a.Num ConsecutiveNums from logs a,logs b,logs c where a.Num=b.Num and b.Num=c.Num
and a.Id=b.Id-1 and b.Id=c.Id-1;

Shell

193. 有效电话号码

grep -P '^(\(\d{3}\) |\d{3}-)\d{3}-\d{4}$' file.txt

195. 第十行

sed -n 10p file.txt