leetcode-sql-刷题汇总(补充ing)

leetcode-sql-刷题汇总

607. 销售员

https://leetcode-cn.com/problems/sales-person/

# 思路1: not in 
select name 
from 
SalesPerson
where name not in 
(select s.name
from 
Company as c
join 
Orders as o 
on c.com_id = o.com_id
join 
SalesPerson as s 
on o.sales_id = s.sales_id
where c.name = 'RED')


# 思路2: 不需要找到人名，只需要找到id，排除id即可
select
    name
from
    salesperson
where
    sales_id not in 
    (select sales_id from orders where com_id in 
        (select com_id from company where name = 'RED'))

197. 上升的温度

https://leetcode-cn.com/problems/rising-temperature/

# Write your MySQL query statement below

# 这个不对，因为数据可能不是日期连续的。。。
select id 
from 
(select 
id, 
lag(Temperature, 1) over (order by recordDate) as preTemperature, Temperature,
lag(recordDate, 1) over (order by recordDate) as preRecordDate, recordDate
from
Weather)as t 
where 1 
and DATE_ADD(preRecordDate, interval 1 day) = recordDate
and Temperature > preTemperature 




# 思路2:就是笛卡尔积，然后判断出来即可
select t2.id 
from 
Weather as t1 
join 
Weather as t2 
where 1
and DATE_ADD(t1.recordDate, interval 1 day) = t2.recordDate
and t2.Temperature > t1.Temperature

1148. 文章浏览 I

https://leetcode-cn.com/problems/article-views-i/

# 思路1: 就where即可
select distinct author_id as id
from Views 
where author_id = viewer_id
order by author_id

1581. 进店却未进行过交易的顾客

https://leetcode-cn.com/problems/customer-who-visited-but-did-not-make-any-transactions/

# 思路1: left join 
select v.customer_id, 
count(1) as count_no_trans
from 
Visits as v 
left join 
Transactions as t 
on v.visit_id = t.visit_id
where t.transaction_id is null
group by v.customer_id

608. 树节点

https://leetcode-cn.com/problems/tree-node/

# 思路1: 就关联出父节点和子节点即可
select id, 
if(p_count > 0 && c_count > 0, 'Inner', if(p_count = 0, 'Root', 'Leaf')) as type
from 
(select t1.id, 
# t2.id as p_id, t3.id as child_id,
count(t2.id) as p_count,
count(t3.id) as c_count
from 
tree as t1 
left join 
tree as t2 
on t1.p_id = t2.id
left join 
tree as t3 
on t3.p_id = t1.id
group by id) as tt 

1795. 每个产品在不同商店的价格(列转行)

https://leetcode-cn.com/problems/rearrange-products-table/

# Write your MySQL query statement below

# 思路1: 常规列转行
#用union all是因为它不去重，这里去不去重都不影响结果，所以选个更少运算的
select product_id, 'store1' as store, store1 as price
from products
where store1 is not null
union all 
select product_id, 'store2' as store, store2 as price
from products
where store2 is not null
union all
select product_id, 'store3' as store, store3 as price
from products
where store3 is not null;


# 思路2: mysql 不太好用
# select product_id,lower(store) as store,price
# from Products
# unpivot (price for store in (store1, store2, store3))

1965. 丢失信息的雇员

https://leetcode-cn.com/problems/employees-with-missing-information/

# 思路1: 常规思路
select employee_id from employees
where employee_id not in (select employee_id from salaries)
union
select employee_id from salaries
where employee_id not in (select employee_id from employees)
order by employee_id

# 思路1: 常规思路
SELECT A.employee_id
FROM employees A LEFT JOIN salaries B ON A.employee_id = B.employee_id
WHERE B.salary IS NULL
UNION
SELECT A.employee_id
FROM salaries A LEFT JOIN employees B ON A.employee_id = B.employee_id
WHERE B.name IS NULL
order by employee_id

# 思路2
with tmp as(
    select employee_id from employees 
    union all
    select employee_id from salaries
)
select
employee_id
from tmp t group by employee_id having count(1)=1 order by employee_id

1527. 患某种疾病的患者

https://leetcode-cn.com/problems/patients-with-a-condition/

# 思路1:这也可以没啥问题
select * from Patients 
where conditions like 'DIAB1%' or conditions like '% DIAB1%'

# 思路2:正则匹配
select 
patient_id,patient_name,conditions
from
Patients
where conditions REGEXP '^DIAB1| +DIAB1'

1484. 按日期分组销售产品

https://leetcode-cn.com/problems/group-sold-products-by-the-date/

select 
sell_date, 
count(distinct product) as num_sold,
group_concat(distinct product order by product) as products
from 
Activities
group by sell_date

1667. 修复表中的名字(首字母转大写)

https://leetcode-cn.com/problems/fix-names-in-a-table/

select user_id, 
concat(upper(SUBSTRING(name,1,1)), lower(SUBSTRING(name,2,length(name)))) as name
from
Users
order by user_id

185. 部门工资前三高的所有员工

https://leetcode-cn.com/problems/department-top-three-salaries/

# 思路1：还是dense_rank的使用
select Department, Employee, Salary
from 
(select d.name as Department, e.name as Employee,  e.salary as Salary,
dense_rank() over (partition by d.id order by salary desc) as dense_rk
from 
Employee as e 
join 
Department as d 
on e.departmentId = d.id) as t
where dense_rk <= 3;

184. 部门工资最高的员工

https://leetcode-cn.com/problems/department-highest-salary/

# 思路1: dense_rank()
select Department, name as Employee, salary
from 
(select d.name as Department, 
e.name, e.salary,
dense_rank() over (partition by d.name order by salary desc) as rn
from 
Employee as e 
join 
Department as d 
on e.departmentId = d.id) as t 
where t.rn = 1;

# 思路2: 原来可以这样的啊
select 
d.Name as Department,
e.Name as Employee,
e.Salary 
from 
Employee e, Department d 
where
e.DepartmentId=d.id 
and
(e.Salary,e.DepartmentId) in 
(select max(Salary),DepartmentId from Employee group by DepartmentId);


# 求出最大工资，然后关联
select t3.name department, t2.name Employee, salary
from (
select departmentid, max(salary) max_salary
from Employee
group by departmentid
) t1
join Employee t2
on t1.departmentid = t2.departmentid and t1.max_salary = t2.salary
join department t3
on t1.departmentid = t3.id

182. 查找重复的电子邮箱

https://leetcode-cn.com/problems/duplicate-emails/

# 思路1
select Email
from Person
group by Email
having count(1) > 1

181. 超过经理收入的员工

https://leetcode-cn.com/problems/employees-earning-more-than-their-managers/

# 这里其实直接join也可以
select emp.name as Employee
from 
Employee as emp
left join 
Employee as man
on emp.managerId = man.id 
where emp.salary > man.salary

196. 删除重复的电子邮箱(保留id最小的)

https://leetcode-cn.com/problems/delete-duplicate-emails/

# 思路1: 直接找到最小id，删除掉not in的数据即可
delete from Person where id not in
(select id from 
(select min(id) as id
from Person
group by email) as t)

627. 变更性别

https://leetcode-cn.com/problems/swap-salary/

思路1: 使用ascii码

思路2: if判断

思路3: case when

# 思路1: 使用ascii码
# update Salary set sex = char(211-ascii(sex));
# update salary set sex = char(ascii('m') + ascii('f') - ascii(sex));

# 思路2: if判断
# update salary set sex=if(sex="m","f","m");

# 思路3: case when
update salary set sex = (case sex when 'm' then 'f' else 'm' end);

1873. 计算特殊奖金

https://leetcode-cn.com/problems/calculate-special-bonus/

select employee_id, 
if(employee_id%2!=0 && name not like 'M%', salary, 0) as bonus
from Employees
order by employee_id

180. 连续出现的数字

https://leetcode-cn.com/problems/consecutive-numbers/

思路1: 分组，把连续出现的相同数组分成一组，然后统计这组的个数，大于3则符合要求筛选出来

# 思路1: 分组，把连续出现的相同数组分成一组，然后统计这组的个数，大于3则符合要求筛选出来
select distinct num as ConsecutiveNums
from
(select num, group_id, 
count(1) as show_times
from
(select id, num, lag_num,
sum(if(lag_num = null or lag_num = num, 0, 1)) over (order by id) as group_id
from (
select id, 
num,
lag(num, 1) over (order by id) as lag_num
from `Logs`
) as t1 ) as t2
group by num, group_id) as t3
where show_times >= 3

思路2: 如果要求是3次，那么其实不用分组：只需要知道：某一行，改行前的num和当前相同，该行后num和当前相同，该num则符合要求

select distinct num as ConsecutiveNums 
from(
select id, num,
  lead(Num) over (order by Id) as nxt_num,
    lag(Num) over(order by Id) as lst_num
FROM Logs)t
where nxt_num=num and lst_num=num

178. 分数排名

https://leetcode-cn.com/problems/rank-scores/

思路1: dense_rank

# 思路1:
# 难道是没有这个函数吗
# 艹，rank是关键字。。。
select score, 
dense_rank() over (order by score desc) as `rank`
from Scores;

# select
#     score,
#     (dense_rank() over (order by Score desc)) AS "rank" 
# from
#     Scores

177. 第N高的薪水

https://leetcode-cn.com/problems/nth-highest-salary/

思路1: row_number

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
        # 思路1
        select (select salary 
        from 
        (select salary, row_number() over (order by salary desc) as rn
        from (
        select DISTINCT salary from Employee) as r
        ) as t 
        where rn = N ) as SecondHighestSalary
  );
END

思路2: limit offset

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N := N-1;
  RETURN (
        # 思路2
        # 注意：SET N := N-1;
        select (select DISTINCT salary
        from Employee
        order by salary DESC
        limit 1 offset N) as SecondHighestSalary
  );
END

思路3: 严谨点IFNULL

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N := N-1;
  RETURN (
        # 思路3
        # 注意：SET N := N-1;
        select IFNULL((select DISTINCT salary
        from Employee
        order by salary DESC
        limit 1 offset N),NULL) as SecondHighestSalary
  );
END

思路4: group去重

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N := N-1;
  RETURN (
        # 思路4:
        # 注意：SET N := N-1;
        SELECT 
                salary
        FROM 
                employee
        GROUP BY 
                salary
        ORDER BY 
                salary DESC
        LIMIT N, 1
  );
END

176. 第二高的薪水

https://leetcode-cn.com/problems/second-highest-salary/

思路1： row_number

从某张表获取，如果没有数据，结果就没没有了
如果是自查询，没有数据的时候会返回null

select (select salary 
from 
(select salary, row_number() over (order by salary desc) as rn
from (
select DISTINCT salary from Employee) as r
) as t 
where rn = 2 ) as SecondHighestSalary

思路2：limit offset

select (select DISTINCT salary
from Employee
order by salary DESC
limit 1 offset 1) as SecondHighestSalary;

思路3: 和思路2相同，只不过加了ifnull

select IFNULL((select DISTINCT salary
from Employee
order by salary DESC
limit 1 offset 1),NULL) as SecondHighestSalary;

175. 组合两个表

https://leetcode-cn.com/problems/combine-two-tables/

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