SQL刷题记

目录

175. 组合两个表

知识点：左外连接

select firstName,lastName,city,state
from Person Left OUTER JOIN Address ON(Person.personId = Address.personId)

176. 第二高的薪水

本题用MYSQL做,则可以利用limit和offset语句

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

SQL解决代码如下

select max(distinct salary) as SecondHighestSalary
from Employee
where salary < (
    select max(distinct salary)
    from Employee
)

180. 连续出现的数字

三表连接

/* Write your T-SQL query statement below */
select distinct l1.Num as ConsecutiveNums
from Logs as l1,Logs as l2,Logs as l3
where l1.Id=l2.Id-1 and l2.Id=l3.Id-1 and l1.Num = l2.Num and l2.Num = l3.Num

181. 超过经理收入的员工

自身连接

/* Write your T-SQL query statement below */
select e1.name as Employee
from Employee as e1,Employee as e2
where e1.managerId = e2.id and e1.salary > e2.salary

1873. 计算特殊奖金

使用union合并两种结果

/* Write your T-SQL query statement below */
select employee_id,salary as bonus
from Employees
where employee_id%2 = 1 and name not like('M%')
union 
select employee_id,salary*0 as bonus
from Employees
where employee_id%2 = 0 or name like('M%')

196. 删除重复的电子邮箱

嵌套查询

/* 
 Please write a DELETE statement and DO NOT write a SELECT statement.
 Write your T-SQL query statement below
 */
delete
from Person
where id not in(
    select min(P.id)
    from Person as P
    group by P.email
)