leetcode当中数据库相关题目（mysql）【sql入门】

sql入门打卡结束🎉还要复习呜呜呜 好多都是看题解改的😭 

​​​​​​​175. 组合两个表

# Write your MySQL query statement below
select firstName, lastName, city,state 
from Person left join Address
on Person.PersonId = Address.PersonId;

176. 第二高的薪水

# Write your MySQL query statement below
SELECT IFNULL(
    (SELECT DISTINCT salary 
    FROM Employee
    ORDER BY salary DESC
    LIMIT 1 , 1 ),NULL)
 AS  SecondHighestSalary;

 

595. 大的国家

# Write your MySQL query statement below
select name, population, area 
from World
where area >= 3000000 or population >= 25000000

182. 查找重复的电子邮箱

WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。

# Write your MySQL query statement below
select Email
from Person
group by Email
having count(Email) > 1

511. 游戏玩法分析 I

# Write your MySQL query statement below
select player_id, min(event_date) as first_login
from Activity
group by player_id;

584. 寻找用户推荐人

# Write your MySQL query statement below
select name
from customer
where referee_id <> 2 OR referee_id IS NULL;

586. 订单最多的客户

写错了一点点 修修改改 再次学习语法 

# Write your MySQL query statement below
select customer_number
from Orders
group by customer_number 
order by count(*) desc
limit 1;

181. 超过经理收入的员工

内连接 

# Write your MySQL query statement below
select a.name as Employee
from Employee as a inner join Employee as b
where a.managerId = b.id
and a.salary > b.salary;

177. 第N高的薪水

修修改改好几遍 有点难 呜呜呜

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    set N=n-1;
  RETURN (
      # Write your MySQL query statement below.
      select ifnull(
          (select 
          distinct salary 
          from Employee 
          order by salary desc 
          limit N,1)
      ,null)
  );
END

178. 分数排名

# Write your MySQL query statement below
select score, 
dense_rank() over(order by score desc) as 'rank'
from Scores;

1757. 可回收且低脂的产品

简单过滤题 没啥好说的 

# Write your MySQL query statement below
select product_id
from Products
where low_fats = 'Y' and recyclable = 'Y'

 183. 从不订购的客户

外链接+过滤 

# Write your MySQL query statement below
select  a.Name as Customers
from Customers as a left outer join Orders as b
on a.Id = b.CustomerId
where b.CustomerId is null

1873. 计算特殊奖金

两种方法

1）if方法

# Write your MySQL query statement below
select employee_id,
if((employee_id % 2 = 0) or (name like 'M%'), 0, salary) as bonus
from Employees

 2）case when

# Write your MySQL query statement below
select employee_id, 
    case when Employees.employee_id % 2 != 1
    or substr(name, 1, 1) = 'M' then 0
    else salary 
    end as bonus
from Employees

196. 删除重复的电子邮箱

涉及两张表的删除，用自连接 

# Please write a DELETE statement and DO NOT write a SELECT statement.
# Write your MySQL query statement below
delete p1 from Person p1, Person p2
where p1.email = p2.email and p1.id > p2.id

627. 变更性别

更新 +if/case when 

# Write your MySQL query statement below
update Salary
set sex = if(sex = 'm', 'f', 'm');

# Write your MySQL query statement below
update Salary
set sex = (case sex when 'm' then 'f' else 'm' end);

1667. 修复表中的名字

upper(),lower(),substring(),concat()函数 

# Write your MySQL query statement below
select user_id,
    concat(upper(substring(name, 1, 1)), lower(substring(name, 2))) as 'name'
from Users
order by user_id asc

1484. 按日期分组销售产品

好烦  照着答案又是修修改改 

# Write your MySQL query statement below
select sell_date,
    count(distinct product) num_sold,
    GROUP_CONCAT(distinct product) products
from Activities
group by sell_date
order by sell_date

 1527. 患某种疾病的患者

重拳出击简单题 通配符 芜湖 

# Write your MySQL query statement below
select patient_id, patient_name, conditions
from Patients
where conditions like '% DIAB1%' or conditions like 'DIAB1%'

1148. 文章浏览 I

简单题重拳出击 idstinct和order by

# Write your MySQL query statement below
select distinct author_id as id 
from Views
where author_id = viewer_id
order by id

1965. 丢失信息的雇员

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

 1795. 每个产品在不同商店的价格

行转列，列转行常用：

1. CASE WHEN
2. UNION ALL

# Write your MySQL query statement below
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

197. 上升的温度

DATEDIFF()

计算两个日期之差

# Write your MySQL query statement below
select a.id
from Weather as a join Weather as b
on datediff(a.recordDate, b.recordDate) = 1 and a.Temperature > b.Temperature

 607. 销售员

两层select嵌套 第一次写错了 照着题解修修改改

# Write your MySQL query statement below
select a.name as name
from SalesPerson as a  
where a.sales_id not in(
    select sales_id
    from Orders as b left join Company as c
    on b.com_id = c.com_id
    where c. name = 'RED'
);

1581. 进店却未进行过交易的顾客
COUNT + LEFT JOIN + IS NULL + GROUP BY

select a.customer_id, count(customer_id) as count_no_trans
from Visits as a left join Transactions as b
on a.visit_id = b.visit_id
where amount is null
group by customer_id

608. 树节点

学习一下case when

# Write your MySQL query statement below
select id, 
    case when p_id is null then 'Root'
    when id in(select distinct p_id from tree)then 'Inner'
    else 'Leaf'
    end as Type
from tree
order by id

1141. 查询近30天活跃用户数

count + datediff + distinct + group by 

# Write your MySQL query statement below
select activity_date as day,  count(distinct user_id) as active_users
from Activity
where datediff('2019-07-27', activity_date) < 30
group by activity_date

1693. 每天的领导和合伙人

主要是distinct + group by

select date_id, make_name, count(distinct lead_id) as unique_leads, count(distinct partner_id) as unique_partners
from DailySales
group by date_id , make_name

1729. 求关注者的数量

简单题重拳出击 没啥好说的 希望早日啥题都会做 都能ak

# Write your MySQL query statement below
select user_id,count(distinct follower_id) as followers_count
from Followers
group by user_id 
order by user_id

1890. 2020年最后一次登录

 where 过滤 + group by 分组

# Write your MySQL query statement below
select user_id, max(time_stamp) as last_stamp
from Logins
where year (time_stamp) = '2020'
group by user_id

1741. 查找每个员工花费的总时间

# Write your MySQL query statement below
select event_day as day, emp_id, sum(out_time - in_time) as total_time
from Employees
group by emp_id, event_day

 1407. 排名靠前的旅行者

写错了 又是修修改改

# Write your MySQL query statement below
select name, coalesce(sum(b.distance),0) as travelled_distance
from Users as a left join Rides as b
on a.id =  b.user_id
group by name
order by travelled_distance desc, name;

1393. 股票的资本损益

case when then end 呜呜呜

# Write your MySQL query statement below
select stock_name, sum(case when operation='Buy' then -price else price end)as capital_gain_loss
from Stocks
group by stock_name

 1158. 市场分析 I

又是做错照着题解改的一天 

# Write your MySQL query statement below
SELECT u.user_id AS buyer_id, u.join_date, IFNULL(a.total,0) AS orders_in_2019
FROM Users u
LEFT JOIN
(SELECT buyer_id AS id, YEAR(order_date) AS year, COUNT(order_id) AS total
FROM Orders 
GROUP BY buyer_id, YEAR(order_date)) a
ON u.user_id = a.id AND a.year = '2019'

1050. 合作过至少三次的演员和导演

having过滤分组

# Write your MySQL query statement below
select actor_id,  director_id
from ActorDirector
group by actor_id,  director_id
having count(*) >= 3;

1587. 银行账户概要 II

 简单题重拳出击 下次不要忘记 on

# Write your MySQL query statement below
select u.name as NAME, sum(amount) as BALANCE
from Users as u left join Transactions as t
on u.account = t.account
group by t.account
having sum(amount) > 10000

1084. 销售分析III

SELECT s.product_id, product_name
FROM Sales s
JOIN Product p
ON s.product_id = p.product_id
GROUP BY s.product_id
HAVING MIN(sale_date) >= '2019-01-01' AND MAX(sale_date) <= '2019-03-31'

620. 有趣的电影

# Write your MySQL query statement below
select *
from cinema
where description not like 'boring' and id % 2 != 0
order by rating desc