# Mysql5.7刷题

# 目录

目录
力扣MySQL题解
力扣-SQL入门
力扣-SQL 基础plus会员-11天
免费题
mid中等
easy简单
4.MySQL必知必会-牛客
《SQL必知必会》配套题单-题解
⭐️总结记忆⭐️
SQL大厂面试真题
面试
参考资料

# 力扣MySQL题解

# 力扣-SQL入门

# 1.选择

# 595. 大的国家 (opens new window)

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

1
2
3
4

# 1757. 可回收且低脂的产品 (opens new window)

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

1
2
3
4

# 584. 寻找用户推荐人 (opens new window)

易错，本题，必须判断referee_id is null

# Write your MySQL query statement below
select name 
from customer
where ( not referee_id=2 ) or  referee_id is null

1
2
3
4

# 183. 从不订购的客户 (opens new window)⭐️

not in的使用

# Write your MySQL query statement below
select name as Customers
from Customers
where Id not in
( 
    select CustomerId
    from Orders
)

1
2
3
4
5
6
7
8

更加明确的写法如下⭐️⭐️

select Customers.name as Customers
from Customers
where Customers.id not in
(
    select Customerid from Orders
)

1
2
3
4
5
6
7

# 2.排序 & 修改

# 1873. 计算特殊奖金 (opens new window)⭐️

学习case end的使用

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

1
2
3
4
5
6
7
8
9

# 627. 变更性别 (opens new window)⭐️

if语句

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

1
2
3
4
5

case解法

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

1
2
3
4
5
6
7

# 196. 删除重复的电子邮箱 (opens new window)⭐️

自连接！学习⭐️⭐️

# 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

1
2
3
4
5

# 3.字符串处理函数/正则

# 1667. 修复表中的名字 (opens new window)

# Write your MySQL query statement below
SELECT user_id, 
    CONCAT(UPPER(SUBSTRING(name, 1, 1)),
         LOWER(SUBSTRING(name, 2, (SELECT LENGTH(name))))) name
FROM Users
ORDER BY user_id

1
2
3
4
5
6
7

# 1484. 按日期分组销售产品 (opens new window)

记忆，不会的GROUP_CONCAT（）函数

select 
    sell_date, 
    # 获取“不同的”产品数【count(distinct product)】
    count(distinct product) as num_sold, 
    # “不同的”【distinct product】产品按照字典排序【order by product】  & “,”分割【separator ','】
    group_concat(distinct product order by product separator ',') as products
from Activities
group by sell_date
order by sell_date;

1
2
3
4
5
6
7
8
9

# 1527. 患某种疾病的患者 (opens new window)

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

1
2
3
4
5

# 4.组合查询&指定选取

# 1965. 丢失信息的雇员 (opens new window)

# Write your MySQL query statement below
select employee_id from (
    select e.employee_id employee_id from Employees e left join Salaries s on e.employee_id=s.employee_id where salary is null
    union all
    select s.employee_id employee_id from Employees e right join Salaries s on e.employee_id=s.employee_id where name is null
) x order by employee_id;

1
2
3
4
5
6
7

# 1795. 每个产品在不同商店的价格 (opens new window)

# Write your MySQL query statement below
select product_id, 'store1' store, store1 price from products where store1 is not NULL
UNION
select product_id, 'store2' store, store2 price from products where store2 is not NULL
UNION
select product_id, 'store3' store, store3 price from products where store3 is not NULL

1
2
3
4
5
6
7
8

# 608. 树节点 (opens new window)

# Write your MySQL query statement below
# Write your MySQL query statement below
select id,(
    case WHEN p_id is null THEN 'Root'
        WHEN id in(select p_id from tree) THEN 'Inner'
        else 'Leaf' END)
    as type from tree 
order by id

1
2
3
4
5
6
7
8

# 176. 第二高的薪水 (opens new window)

# Write your MySQL query statement below
SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary
;

1
2
3
4
5
6
7
8
9
10

# 5.合并

多表的联结又分为以下几种类型：

1）左联结（left join），联结结果保留左表的全部数据

2）右联结（right join），联结结果保留右表的全部数据

3）内联结（inner join），取两表的公共数据

# 175. 组合两个表 (opens new window)

# Write your MySQL query statement below
select FirstName, LastName, City, State
from Person left join Address
on Person.PersonId = Address.PersonId

1
2
3
4

# 1581. 进店却未进行过交易的顾客 (opens new window)

# Write your MySQL query statement below
select a.customer_id, count(1) count_no_trans 
    from Visits a
    where a.visit_id not in (select b.visit_id from Transactions b)
    group by a.customer_id

1
2
3
4
5
6

# 1148. 文章浏览 I (opens new window)

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

1
2
3
4
5
6

# 6.合并

# 197. 上升的温度 (opens new window)

# Write your MySQL query statement below
SELECT b.Id
FROM Weather as a,Weather as b
WHERE a.Temperature < b.Temperature and DATEDIFF(a.RecordDate,b.RecordDate) = -1;

1
2
3
4

# 607. 销售员 (opens new window)

select distinct name from SalesPerson where sales_id not in (
    select distinct sales_id from Orders where com_id=(
        select com_id from Company where name="RED"
    )
);

1
2
3
4
5

# 7.计算函数

# 1141. 查询近30天活跃用户数 (opens new window)

# Write your MySQL query statement below
# Write your MySQL query statement below
select activity_date as day,count(distinct user_id) as active_users
from
    Activity
where
    activity_date between '2019-06-28' and '2019-07-27'
group by
    activity_date;

1
2
3
4
5
6
7
8
9

# 1693. 每天的领导和合伙人 (opens new window)

# Write your MySQL query statement below
select date_id , make_name ,count(distinct lead_id) unique_leads , count(distinct partner_id) unique_partners
from dailysales
group by date_id,make_name;

1
2
3
4
5

# 1729. 求关注者的数量 (opens new window)

# Write your MySQL query statement below
select user_id, count(1) followers_count 
from Followers 
group by user_id 
order by user_id;

1
2
3
4
5

# 8.计算函数

# 511. 游戏玩法分析 I (opens new window)

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

1
2
3
4

# 586. 订单最多的客户 (opens new window)

SELECT
    customer_number
FROM
    orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC
LIMIT 1

1
2
3
4
5
6
7

# 1890. 2020年最后一次登录 (opens new window)

# Write your MySQL query statement below
# Write your MySQL query statement below
select
    user_id,max(time_stamp) as last_stamp 
from
    Logins
where
    time_stamp between '2020-01-01' and '2021-01-01'
group by 
    user_id

1
2
3
4
5
6
7
8
9
10
11

# 1741. 查找每个员工花费的总时间 (opens new window)

SELECT
    event_day day, emp_id, SUM(out_time - in_time) total_time
FROM
    Employees
GROUP BY
    event_day, emp_id;

1
2
3
4
5
6
7

# 9.控制流

# 1393. 股票的资本损益 (opens new window)

# Write your MySQL query statement below
# Write your MySQL query statement below
# 方法一 case when
# select stock_name, sum(
#     case when operation = 'buy' then -price
#     else price end
# ) as capital_gain_loss 
# from stocks 
# group by stock_name;

# 方法二 if语句
select stock_name, sum(
    if(operation = 'buy', -price, price)
) as capital_gain_loss 
from stocks 
group by stock_name;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 1407. 排名靠前的旅行者 (opens new window)

# Write your MySQL query statement below
# Write your MySQL query statement below
# 1.先查出每个有行程的用户的距离 用到group by 和sum
# 2.由于没有行程的用户也需要查找所以需用到left join(主表数据不受影响),
#   再通过IFNULL()函数对行程为null的用户赋值为0,最后对其order by排序就行了
select name, ifnull (travelled_distance,0) as travelled_distance
from Users

left join
(
    select
    user_id,sum(distance) as travelled_distance 
    from Rides 
        group by user_id
)t1
on t1.user_id= Users.id
order by travelled_distance desc, name asc

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 1158. 市场分析 I (opens new window)

# Write your MySQL query statement below
select Users.user_id as buyer_id, 
    join_date, 
    ifnull(UserBuy.cnt, 0) as orders_in_2019
from Users
left join (
    select buyer_id, count(order_id) cnt 
    from Orders
    where order_date between '2019-01-01' and '2019-12-31'
    group by buyer_id
) UserBuy
on Users.user_id = UserBuy.buyer_id

1
2
3
4
5
6
7
8
9
10
11
12
13

# 10.过滤

# 182. 查找重复的电子邮箱 (opens new window)

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

1
2
3
4
5

# 1050. 合作过至少三次的演员和导演 (opens new window)

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

1
2
3
4
5

# 1587. 银行账户概要 II (opens new window)

# Write your MySQL query statement below
select    name, SUM(amount) balance
from
    Users u 
join
    Transactions t 
on
    u.account = T.account
group by
    u.account
having
    sum(amount) > 10000;

1
2
3
4
5
6
7
8
9
10
11
12

# 1084. 销售分析III (opens new window)

select product_id, product_name
from product
where product_id in (
    select
        product_id
    from sales
    group by product_id
    having max(sale_date) <= '2019-03-31' and min(sale_date) >= '2019-01-01'
)

1
2
3
4
5
6
7
8
9

# 力扣-SQL 基础plus会员-11天

# 1.数值处理函数

# 1699. 两人之间的通话次数 (opens new window)

# Write your MySQL query statement below
# Write your MySQL query statement below
SELECT 
    person1,person2, 
    count(*) call_count, 
    sum(duration) total_duration 
FROM (
SELECT 
    IF(from_id>to_id, to_id, from_id) person1, 
    IF(from_id>to_id,from_id,to_id) person2, 
    duration  
FROM calls 
) c 
GROUP BY 
    person1, person2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 1251. 平均售价 (opens new window)

SELECT u.product_id product_id,
        ROUND(SUM(u.units*p.price)/SUM(u.units),2) average_price        
FROM UnitsSold u ,Prices p
WHERE u.product_id=p.product_id 
        AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY u.product_id

1
2
3
4
5
6
7

# 1571. 仓库经理 (opens new window)

# Write your MySQL query statement below
SELECT 
    w.name WAREHOUSE_NAME,
    SUM(p.Width * p.Length * p.Height * w.units) VOLUME
FROM 
    warehouse w
LEFT JOIN 
    products p
ON 
    w.product_id = p.product_id
GROUP BY w.name;

1
2
3
4
5
6
7
8
9
10
11
12

# 1445. 苹果和桔子 (opens new window)

select    sale_date,
    sum(
        if(fruit = 'apples', sold_num, -sold_num)
    ) as diff
    from sales
group by sale_date

1
2
3
4
5
6

# 2.数值处理函数

# 1193. 每月交易 I (opens new window)

select date_format(trans_date,'%Y-%m') as month,
        country, count(*) as trans_count, 
        sum(case state when 'approved' then 1 else 0 end) as approved_count,
        sum(amount) as trans_total_amount,
        sum(case state when 'approved' then amount else 0 end) as approved_total_amount
from Transactions
group by month,country;

1
2
3
4
5
6
7

# 1633. 各赛事的用户注册率 (opens new window)

# Write your MySQL query statement below

select contest_id,
       round(count(user_id) / (select count(1) from Users) * 100, 2) as percentage
       from Register
       group by contest_id
       order by percentage desc , contest_id

1
2
3
4
5
6
7
8

# 1173. 即时食物配送 I (opens new window)

# Write your MySQL query statement below
select round (
    sum(order_date = customer_pref_delivery_date) /
    count(*) * 100,
    2
) as immediate_percentage
from Delivery

1
2
3
4
5
6
7
8

# 1211. 查询结果的质量和占比 (opens new window)

# Write your MySQL query statement below
select 
query_name, 
        round(avg(rating/position), 2) quality,
        round(sum(if(rating < 3, 1, 0)) * 100 / count(*), 2) poor_query_percentage
        from queries
        group by query_name

1
2
3
4
5
6
7
8

# 3.连接

# 1607. 没有卖出的卖家 (opens new window)

/* write your pl/sql query statement below */
select s.seller_name from seller s where s.seller_id not in (
    select o.seller_id from orders o where o.sale_date like '2020%'
) order by seller_name

1
2
3
4
5

# 619. 只出现一次的最大数字 (opens new window)

select 
(
    select ifnull(num, null) as num from mynumbers 
    group by num
    having count(*) = 1
    order by num desc
    limit 0, 1
) num

1
2
3
4
5
6
7
8
9

# 1112. 每位学生的最高成绩 (opens new window)

SELECT student_id, min(course_id) AS course_id, grade
FROM enrollments
WHERE (student_id, grade) IN
(
    SELECT student_id, MAX(grade) AS grade
    FROM Enrollments
    GROUP BY student_id
)
GROUP BY student_id, grade
ORDER BY student_id

1
2
3
4
5
6
7
8
9
10
11

# 1398. 购买了产品 A 和产品 B 却没有购买产品 C 的顾客 (opens new window)

select o.customer_id, c.customer_name from orders o
left join customers c
on o.customer_id = c.customer_id
group by customer_id
having 
    SUM(if(product_name = 'A', 1, 0)) > 0 and
    SUM(if(product_name = 'B', 1, 0)) > 0 and
    SUM(if(product_name = 'C', 1, 0)) = 0

1
2
3
4
5
6
7
8

# 4.连接

# 1440. 计算布尔表达式的值 (opens new window)

select e.left_operand,e.operator,e.right_operand,
case e.operator
    when '>' then if(v1.value>v2.value,'true','false')
    when '<' then if(v1.value<v2.value,'true','false')
    else  if(v1.value=v2.value,'true','false')
end value
    
from Expressions e
left join Variables v1 on v1.name = e.left_operand 
left join Variables v2 on v2.name = e.right_operand

1
2
3
4
5
6
7
8
9
10
11

# 1264. 页面推荐 (opens new window)

select distinct page_id as recommended_page
from likes
where user_id in (
                select user1_id as user_id from friendship where user2_id = 1
                union all
                select user2_id as user_id from friendship where user1_id = 1
                ) and page_id not in (
                        select page_id from likes where user_id = 1
                        )

1
2
3
4
5
6
7
8
9

# 570. 至少有5名直接下属的经理 (opens new window)

select
name
from
employee as t1 join
(select
 managerid
 from
 employee
 group by managerid
 having count(managerid) >= 5) as t2
on t1.id = t2.managerid

1
2
3
4
5
6
7
8
9
10
11
12

# 1303. 求团队人数 (opens new window)

# write your mysql query statement below
select e.employee_id,t1.team_size from employee e inner join 
(
    select team_id, 
    count(team_id) as team_size from employee group by team_id
) t1
on t1.team_id = e.team_id

1
2
3
4
5
6
7
8

# 5.连接

# 1280. 学生们参加各科测试的次数 (opens new window)

# 自连接
with cte1 as
(
    select *
    from students,subjects
)


select 
    a.student_id,
    a.student_name,
    a.subject_name,
    ifnull(count(b.subject_name),0) as attended_exams 
from cte1 a
left join Examinations b
using(student_id,subject_name)
group by 1,2,3
order by 1,2,3

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

# 1501. 可以放心投资的国家 (opens new window)

# write your mysql query statement below
select c.name as country
	from calls, person, country c
where (caller_id = id or callee_id = id) and country_code = left(phone_number, 3)
	group by country_code
	having avg(duration) > (select avg(duration) from calls);

1
2
3
4
5
6

# 184. 部门工资最高的员工 (opens new window)

select
    department.name as 'department',
    employee.name as 'employee',
    salary
from
    employee
        join
    department on employee.departmentid = department.id
where
    (employee.departmentid , salary) in
    (   select
            departmentid, max(salary)
        from
            employee
        group by departmentid
	)
;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# 580. 统计各专业学生人数 (opens new window)

select
    dept_name, count(student_id) as student_number
from
    department
        left outer join
    student on department.dept_id = student.dept_id
group by department.dept_name
order by student_number desc , department.dept_name
;

1
2
3
4
5
6
7
8
9
10

# 6.连接

# 1294. 不同国家的天气类型 (opens new window)

# Write your MySQL query statement below
SELECT country_name AS 'country_name',
    CASE 
        WHEN AVG(w1.weather_state) <= 15 THEN 'Cold' 
        WHEN AVG(w1.weather_state) >= 25 THEN 'Hot'
        ELSE 'Warm'
    END
        AS 'weather_type'
FROM 
    Countries AS c1
    INNER JOIN Weather AS w1
    ON c1.country_id = w1.country_id
WHERE w1.day BETWEEN '2019-11-01' AND '2019-11-30'
GROUP BY c1.country_id
;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 626. 换座位 (opens new window)

select
    (case
        when mod(id, 2) != 0 and counts != id then id + 1
        when mod(id, 2) != 0 and counts = id then id
        else id - 1
    end) as id,
    student
from
    seat,
    (select
        count(*) as counts
    from
        seat) as seat_counts
order by id asc;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 1783. 大满贯数量 (opens new window)

# Write your MySQL query statement below
#计算Championships表中每个运动员id出现的次数

SELECT player_id,player_name,
      SUM(IF(player_id=Wimbledon,1,0)+IF(player_id=Fr_open,1,0)+IF(player_id=US_open,1,0)+IF(player_id=Au_open,1,0)) AS grand_slams_count
FROM Championships 
JOIN Players 
GROUP BY player_id
HAVING grand_slams_count>0

1
2
3
4
5
6
7
8
9

# 1164. 指定日期的产品价格 (opens new window)

select p1.product_id, ifnull(p2.new_price, 10) as price
from (
    select distinct product_id
    from products
) as p1 -- 所有的产品
left join (
    select product_id, new_price 
    from products
    where (product_id, change_date) in (
        select product_id, max(change_date)
        from products
        where change_date <= '2019-08-16'
        group by product_id
    )
) as p2 -- 在 2019-08-16 之前有过修改的产品和最新的价格
on p1.product_id = p2.product_id

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 7.连接

# 603. 连续空余座位 (opens new window)

# Write your MySQL query statement below
select distinct a.seat_id
from cinema a join cinema b
  on abs(a.seat_id - b.seat_id) = 1
  and a.free = true and b.free = true
order by a.seat_id
;

1
2
3
4
5
6
7

# 1731. 每位经理的下属员工数量 (opens new window)

# Write your MySQL query statement below
select 
    e1.employee_id,
    e1.name,
    count(*) as reports_count,
    ROUND(avg(e2.age), 0) as average_age
from employees e1
join employees e2
on e2.reports_to = e1.employee_id
group by employee_id
order by employee_id

1
2
3
4
5
6
7
8
9
10
11
12

# 1747. 应该被禁止的 Leetflex 账户 (opens new window)

# Write your MySQL query statement below
select distinct l1.account_id from loginfo l1
join loginfo l2
on 
    l1.account_id = l2.account_id and 
    l1.ip_address != l2.ip_address and 
    ( l1.login >= l2.login and l1.login <= l2.logout )

1
2
3
4
5
6
7
8

# 181. 超过经理收入的员工 (opens new window)

select
     a.name as employee
from employee as a join employee as b
     on a.managerid = b.id
     and a.salary > b.salary
;

1
2
3
4
5
6

# 8.不等式连接

# 1459. 矩形面积 (opens new window)

# Write your MySQL query statement below
SELECT 
    p1.id P1,
    p2.id P2,
    ABS(p1.x_value-p2.x_value)*ABS(p1.y_value-p2.y_value) AREA
FROM points p1,points p2
WHERE p1.id<p2.id 
AND p1.x_value!=p2.x_value
AND p1.y_value!=p2.y_value
ORDER BY  AREA desc,p1.id,p2.id

1
2
3
4
5
6
7
8
9
10
11

# 180. 连续出现的数字 (opens new window)

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num
;

1
2
3
4
5
6
7
8
9
10
11
12

# 1988. 找出每所学校的最低分数要求 (opens new window)

# Write your MySQL query statement below
SELECT
    school_id,
    IFNULL(min(score), -1) as score
FROM (
    SELECT
        s.school_id,
        e.score
    FROM schools s
    LEFT JOIN exam e ON s.capacity >= e.student_count
) t
GROUP BY school_id
ORDER BY school_id;

1
2
3
4
5
6
7
8
9
10
11
12
13

# 9.子查询

# 1549. 每件商品的最新订单 (opens new window)

select p.product_name, o.product_id, o.order_id, o.order_date from orders o
left join products p
on p.product_id = o.product_id
where (o.product_id, order_date) in 
(
    select product_id, max(order_date) from orders
    group by product_id
)
order by product_name asc, product_id asc, order_id asc

1
2
3
4
5
6
7
8
9
10

# 1321. 餐馆营业额变化增长 (opens new window)

select 
distinct a.visited_on,sum(b.amount) as amount,round(sum(b.amount)/7,2) as average_amount 
from Customer a
join Customer b
on datediff(a.visited_on,b.visited_on) <= 6 and datediff(a.visited_on,b.visited_on) >= 0
group by a.visited_on,a.customer_id     
having  count(distinct b.visited_on) = 7
order by visited_on

1
2
3
4
5
6
7
8

# 1045. 买下所有产品的客户 (opens new window)

select customer_id from customer
group by customer_id
having count(distinct product_key) = (select count(distinct product_key) from product)

1
2
3
4

# 10.子查询

# 1341. 电影评分 (opens new window)

select T.results FROM 
(
    SELECT U.name AS results,MR.user_id
    FROM Users U,MovieRating MR 
    WHERE U.user_id = MR.user_id
    GROUP BY MR.user_id,U.name
    ORDER BY COUNT(1) DESC,U.NAME ASC
    limit 1
) T
union all
select Q.results FROM 
(
    SELECT M.title AS results
    FROM Movies M,MovieRating MM 
    WHERE M.movie_id = MM.movie_id 
    AND MM.created_at LIKE '2020-02%'
    GROUP BY M.title
    ORDER BY AVG(MM.rating) DESC,M.title ASC
    limit 1
) Q

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

# 1867. 最大数量高于平均水平的订单 (opens new window)

select
    order_id
from (
    -- 每个订单的最大数量 > max(每个订单的平均数量)
    select
        *,
        max(quantity) > max(avg(quantity)) over() as flag
    from OrdersDetails
    group by order_id
) as base
where flag = 1

1
2
3
4
5
6
7
8
9
10
11

# 11.子查询

# 550. 游戏玩法分析 IV (opens new window)

# Write your MySQL query statement below
select round(avg(a.event_date is not null), 2) fraction
from 
    (select player_id, min(event_date) as login
    from activity
    group by player_id) p 
left join activity a 
on p.player_id=a.player_id and datediff(a.event_date, p.login)=1

1
2
3
4
5
6
7
8
9

# 262. 行程和用户 (opens new window)

select request_at 'Day',
round(count(if(status!='completed',status,null))/count(*),2) 'Cancellation Rate'
from Trips
where request_at between '2013-10-01' and '2013-10-03'
and client_id not in (select users_id from Users where banned='Yes') 
and driver_id not in (select users_id from Users where banned='Yes')
group by request_at;

1
2
3
4
5
6
7
8

# 免费题

# mid中等

# 1070. 产品销售分析 III (opens new window)「学习」

# Write your MySQL query statement below
select 
    product_id, 
    year as first_year, 
    quantity, 
    price
from sales
where (product_id, year) in   #学习！！⭐️
( 
    select product_id, min(year)
    from sales 
    group by product_id
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 178. 分数排名 (opens new window)「学习」

# Write your MySQL query statement below
select a.score , 
    ( select count( distinct score )  #distinct写法可以和后面的一样
      from Scores as b
      where b.score>=a.score
    ) as `rank`                      #rank也可以不加反引号，但是力扣的mysql版本，好像需要你反引号
from Scores as a
order by a.score desc;

1
2
3
4
5
6
7
8

# Write your MySQL query statement below
select a.score , 
    ( select count( distinct(b.score) )
      from Scores as b
      where b.score>=a.score
    ) as `rank`
from Scores as a
order by a.score desc;

1
2
3
4
5
6
7
8

# 1907. 按分类统计薪水 (opens new window)「写法！」

# Write your MySQL query statement below
select 
    'Low Salary' as category,
    count(income) as accounts_count
from accounts 
where income < 20000

union

select 
    'Average Salary' as category,
    count(income ) as accounts_count
from accounts
where income <= 50000 and income >= 20000

union 

select 
    'High Salary' as category,
    count(income) as accounts_count
from accounts
where income > 50000;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# 1204. 最后一个能进入电梯的人 (opens new window)

# 585. 2016年的投资 (opens new window)⭐️学习！！

下面的语法报错

# Write your MySQL query statement below
select sum( TIV_2016 ) as TIV_2016
from insurance
where count(TIV_2015)>1
and count(LAT)=1;
group by TIV_2015;

1
2
3
4
5
6

需要下面这么写
- 提示：连接 LAT 和 LON 为一个整体，以表示坐标信息。

# Write your MySQL query statement below
SELECT
    round( SUM(insurance.TIV_2016) , 2) AS TIV_2016
FROM
    insurance
WHERE
    insurance.TIV_2015 IN
    (
      SELECT
        TIV_2015
      FROM
        insurance
      GROUP BY TIV_2015
      HAVING COUNT(*) > 1  #group by才能count好像！！！
    )
    AND 
    CONCAT(LAT, LON) IN   
    (
      SELECT
        CONCAT(LAT, LON)
      FROM
        insurance
      GROUP BY LAT , LON
      HAVING COUNT(*) = 1
    )
;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

# 602. 好友申请 II ：谁有最多的好友 (opens new window)「学习union all的特殊技巧」

借助取相同的名字

# Write your MySQL query statement below
select id , count(id) as num
from
(
    select requester_id as id  #借助取相同的名字
    from RequestAccepted
    union all
    select accepter_id as id 
    from RequestAccepted
) as temp  #一定要as temp，不然就会语法报错！！！
group by id
order by num desc
limit 0, 1;

1
2
3
4
5
6
7
8
9
10
11
12
13

# easy简单

# 1978. 上级经理已离职的公司员工 (opens new window)

# Write your MySQL query statement below
select employee_id
from Employees
where 
    salary < 30000
and 
    manager_id not in (
        select employee_id
        from Employees
    )
order by employee_id;

1
2
3
4
5
6
7
8
9
10
11

# 577. 员工奖金 (opens new window)「典型left join」

坑点：bonus值为空，空值在数据库中的表示为null

# Write your MySQL query statement below
select Employee.name , Bonus.bonus
from 
    Employee 
left join
    Bonus
on 
    Employee.empID=Bonus.empID 
where
    ifnull(Bonus.bonus, 0 )<1000;  #学习函数IFnull
    #使用IFNULL函数，如果是Null的话，用一个小于1000的值代替null值，这样就会筛选出bonus为Null的员工

1
2
3
4
5
6
7
8
9
10
11

==等价于下面的判断null的==

# Write your MySQL query statement below
select Employee.name , Bonus.bonus
from 
    Employee 
left join
    Bonus
on 
    Employee.empID=Bonus.empID 
where
    (Bonus.bonus is null) or (Bonus.bonus<1000);

1
2
3
4
5
6
7
8
9
10

# 620. 有趣的电影 (opens new window)

# Write your MySQL query statement below
select  id, movie, description, rating
from cinema
where 
    'boring'!=description
and 
    1=mod(id,2)
order by rating desc;

1
2
3
4
5
6
7
8

# Write your MySQL query statement below
select  id, movie, description, rating
from cinema
where 
    'boring'!=description
and 
    1=id%2
order by rating desc;

1
2
3
4
5
6
7
8

# 1075. 项目员工 I (opens new window)

round函数+group by要指定对
查询每一个项目中员工的平均工作年限，精确到小数点后两位。

# Write your MySQL query statement below
select 
    Project.project_id, 
    round( avg(Employee.experience_years), 2) as average_years 
from 
    Project, Employee
where Project.employee_id=Employee.employee_id
group by Project.project_id;

1
2
3
4
5
6
7
8
9
10

inner join

# Write your MySQL query statement below
select 
    Project.project_id, 
    round( avg(Employee.experience_years), 2) as average_years 
from 
    Project
inner join
    Employee
where Project.employee_id=Employee.employee_id
group by Project.project_id;

1
2
3
4
5
6
7
8
9
10

join

# Write your MySQL query statement below
select 
    Project.project_id, 
    round( avg(Employee.experience_years), 2) as average_years 
from 
    Project
join
    Employee
where Project.employee_id=Employee.employee_id
group by Project.project_id;

1
2
3
4
5
6
7
8
9
10

# 1683. 无效的推文 (opens new window)-length函数

# Write your MySQL query statement below
select tweet_id 
from Tweets
where length( content )>15;

1
2
3
4

# 1378. 使用唯一标识码替换员工ID (opens new window)

# Write your MySQL query statement below
select EmployeeUNI.unique_id , Employees.name
from 
    Employees
left join
    EmployeeUNI
on Employees.id=EmployeeUNI.id;

1
2
3
4
5
6
7

# 2356. 每位教师所教授的科目种类的数量 (opens new window)

# Write your MySQL query statement below
select teacher_id , count( distinct(subject_id) )cnt
from Teacher
group by teacher_id;

1
2
3
4

# 610. 判断三角形 (opens new window)-分支判断-case end⭐️

# Write your MySQL query statement below
select x, y, z , 
( 
    case
        when x+y>z and x+z>y and y+z>x 
        then 'Yes' 
        else 'No'
    end
)
as triangle
from Triangle;

1
2
3
4
5
6
7
8
9
10
11

# 1517. 查找拥有有效邮箱的用户 (opens new window)-正则表达式⭐️⭐️⭐️

参考资料：https://leetcode.cn/problems/find-users-with-valid-e-mails/solution/xue-xi-liao-zheng-ze-biao-da-shi-yao-yon-hl2c/

^ 表示以后面的字符为开头
[] 表示括号内任意字符

- 表示连续
* 表示重复前面任意字符任意次数
  \ 用来转义后面的特殊字符，以表示字符原本的样子，而不是将其作为特殊字符使用
  $ 表示以前面的字符为结尾

前缀名以字母开头：^[a-zA-Z]
前缀名包含字母（大写或小写）、数字、下划线_、句点. 和 或 横杠-：[a-zA-Z0-9\_\.\-]*
以域名'@leetcode.com'结尾：@leetcode\.com$

1
2
3
4
5
6
7
8
9
10
11

# Write your MySQL query statement below
select 
    *
from Users
where mail regexp '^[a-zA-Z]+[\\w./-]*@leetcode\\.com$';

1
2
3
4
5
6

'[a-zA-Z0-9_]'简写'[\w]'

# 1068. 产品销售分析 I (opens new window)-写法

inner join

# Write your MySQL query statement below
select Product.product_name, Sales.year, Sales.price
from 
    Sales
inner join
    Product
using(product_id) ;

1
2
3
4
5
6
7

# Write your MySQL query statement below
select Product.product_name, Sales.year, Sales.price
from 
    Sales
inner join
    Product
on Sales.product_id=Product.product_id ;

1
2
3
4
5
6
7

where

# Write your MySQL query statement below
select Product.product_name, Sales.year, Sales.price
from 
    Sales, Product
where Sales.product_id=Product.product_id ;

1
2
3
4
5

# 1789. 员工的直属部门 (opens new window)-难点⭐️

联合查询，自动去重

# Write your MySQL query statement below
SELECT employee_id AS 'employee_id', 
    department_id AS 'department_id'
FROM Employee 
WHERE primary_flag = 'Y'

UNION               

SELECT employee_id AS 'employee_id', 
    department_id AS 'department_id'
FROM Employee
GROUP BY employee_id
HAVING COUNT(department_id) = 1;

1
2
3
4
5
6
7
8
9
10
11
12
13

# 1661. 每台机器的进程平均运行时间 (opens new window)-难点-复杂逻辑-⭐️

AVG() 函数返回数值列的平均值。

# Write your MySQL query statement below
select
    machine_id,
    round( 
        avg(   if(activity_type='start', -timestamp, timestamp)  )*2
        , 3) processing_time 
from Activity 
group by machine_id;

1
2
3
4
5
6
7
8

# Write your MySQL query statement below
select machine_id,
    round( 
        avg( if( 'start'=activity_type , -timestamp, timestamp)) *2 
        ,3) as processing_time
from Activity
group by machine_id;

1
2
3
4
5
6
7

# 1327. 列出指定时间段内所有的下单产品 (opens new window)-

# Write your MySQL query statement below

select Products.product_name, sum( Orders.unit ) as unit
from Products, Orders
where 
    Products.product_id=Orders.product_id
and 
    Orders.order_date like '2020-02-%'
group by Orders.product_id
having sum( Orders.unit )>=100;

1
2
3
4
5
6
7
8
9
10

# 4.MySQL必知必会-牛客

主要以MySQL的语法解决问题
用实战的方式，熟悉SQL语法

# 《SQL必知必会》配套题单-题解

链接 (opens new window)

# 1.检索数据

# 60.✔️从 Customers 表中检索所有的 ID

select cust_id from Customers

# 61.✔️检索并列出已订购产品的清单 (opens new window)「去重」

记忆：distinct, adj,不同种类的;
- 去重！

select distinct(prod_id) from OrderItems

# 62. 检索所有列

select * from Customers

# 2.排序检索数据「order by」

# 63. ✔️检索顾客名称并且排序 (opens new window)

逆序，desc
正序，asc

select cust_name 
from Customers 
order by cust_name desc

1
2
3

# 64. ✔️对顾客ID和日期排序 (opens new window)

从 Orders 表中检索顾客 ID（cust_id）和订单号（order_num），并先按顾客 ID 对结果进行排序，再按订单日期倒序排列

select cust_id , order_num 
from Orders 
order by cust_id , order_date desc

1
2
3

# 65. ✔️按照数量和价格排序 (opens new window)

==易错：注意，每列都需要单独的desc或者asc==

select quantity , item_price 
from OrderItems
order by quantity desc, item_price desc

1
2
3

# 66. 检查SQL语句

SELECT vend_name
FROM Vendors 
ORDER BY vend_name DESC;

1
2
3

# 3.过滤数据「过滤规则where」

# 67.✔️ 返回固定价格的产品 (opens new window)

select prod_id , prod_name
from Products
where 9.49=prod_price

1
2
3

# 68.✔️返回更高价格的产品 (opens new window)

select prod_id , prod_name
from Products
where prod_price >= 9

1
2
3

# 69.✔️返回产品并且按照价格排序 (opens new window)

select prod_name , prod_price
from Products 
where prod_price between 3 and 6 #表示【3,6】
order by prod_price

1
2
3
4

# 70.返回更多的产品 (opens new window)

select distinct(order_num)
from OrderItems
where quantity >= 100

1
2
3

# 4.高级数据过滤

# 71.检索供应商名称 (opens new window)⭐️「and」

select vend_name
from Vendors
where vend_country='USA' and vend_state='CA'

1
2
3

# 72.检索并列出已订购产品的清单 (opens new window)⭐️「in」

select order_num ,  prod_id , quantity
 from OrderItems 
 where prod_id in ( 'BR01' , 'BR02' , 'BR03' ) and quantity >= 100

1
2
3

# 73.返回所有价格在 3美元到 6美元之间的产品的名称和价格

select prod_name , prod_price
from Products
where prod_price between 3 and 6
order by prod_price

1
2
3
4

# 74.纠错2

SELECT vend_name 
FROM Vendors 
WHERE vend_country = 'USA' AND vend_state = 'CA'
ORDER BY vend_name

1
2
3
4

# 5.用通配符进行过滤「正则表达式」

# 75.检索产品名称和描述 (opens new window)（一）⭐️

仅返回描述中包含 toy 一词的产品名称

select prod_name , prod_desc
from Products
where prod_desc like '%toy%'

1
2
3

# 76.检索产品名称和描述（二）

select prod_name , prod_desc
from Products
where not prod_desc like '%toy%'

1
2
3

# 77.检索产品名称和描述（三）

select prod_name , prod_desc
from Products
where 
prod_desc like '%toy%' 
and 
prod_desc like '%carrots%'

1
2
3
4
5
6

# 78.检索产品名称和描述 (opens new window)（四）⭐️

仅返回在描述中以先后顺序==同时出现 toy 和 carrots 的产品==。提示：只需要用带有三个 % 符号的 LIKE 即可

select prod_name , prod_desc
from Products
where 
prod_desc like '%toy%carrots%'

1
2
3
4

# 6.「别名」

创建计算字段

# 79.别名 (opens new window)⭐️

select vend_id
,vend_name as vname
,vend_address as vaddress
,vend_city as vcity
from Vendors
order by vend_name

1
2
3
4
5
6

# 80.打折 (opens new window)⭐️「别名的高级使用」

sale_price 是一个包含促销价格的计算字段。提示：可以乘以 0.9，得到原价的 90%（即 10%的折扣）

select prod_id , prod_price 
, prod_price*0.9 as sale_price #⭐️⭐️⭐️
from Products

1
2
3

# 7.使用函数处理数据「函数」

# 81.顾客登录名 (opens new window)

select cust_id, cust_name , 
upper( 
    concat( 
        substring(cust_contact,1,2)
        , substring(cust_city,1,3)
        )
    )
as user_login
from Customers

1
2
3
4
5
6
7
8
9

# 82.返回 2020 年 1 月的所有订单的订单号和订单日期

select order_num , order_date
from Orders
where substring(order_date,1,7)='2020-01'
order by order_date

1
2
3
4

# 8.汇总数据「统计」

# 83.确定已售出产品的总数

select sum(quantity) as items_ordered
from OrderItems

1
2

# 84.确定已售出产品项 BR01 的总数

select sum(quantity) as items_ordered
from OrderItems
where prod_id='BR01'

1
2
3

# 85.确定 Products 表中价格不超过 10 美元的最贵产品的价格

select sum(quantity) as items_ordered
from OrderItems
where prod_id='BR01'

1
2
3

# 9.分组数据

# 86.返回每个订单号各有多少行数 (opens new window)

select order_num , count(*) as order_lines
from OrderItems
group by order_num
order by order_lines

1
2
3
4

# 87.每个供应商成本最低的产品

select vend_id, min(prod_price)  as cheapest_item
from Products
group by vend_id
order by cheapest_item

1
2
3
4

# 88.确定最佳顾客

select order_num
from OrderItems
group by order_num
having sum(quantity)>=100
order by order_num

1
2
3
4
5

# 89.确定最佳顾客的另一种方式（一）

select
  order_num,
  sum(quantity * item_price) as total_price
from
  OrderItems
group by
  order_num
having
  sum(quantity * item_price) >= 1000
order by
  order_num

1
2
3
4
5
6
7
8
9
10
11

# 90.纠错3

SELECT order_num, COUNT(*) AS items 
FROM OrderItems 
GROUP BY order_num
HAVING COUNT(*) >= 3 
ORDER BY items, order_num;

1
2
3
4
5

# 10.使用「子查询」

# 91.返回购买价格为 10 美元或以上产品的顾客列表 (opens new window)

select order_num
from OrderItems
group by order_num
having sum(quantity)>=100
order by order_num

1
2
3
4
5

下面的不行！！！必须是上面的where

select order_num
from OrderItems
where sum(quantity)>=100   (错误答案)
group by order_num
order by order_num

1
2
3
4
5

# 92.确定哪些订单购买了 prod_id 为 BR01 的产品（一） (opens new window)

select cust_id,order_date
from Orders
where order_num in
(
select order_num
from OrderItems
where prod_id ='BR01')

1
2
3
4
5
6
7

# 93.返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（一） (opens new window)

select cust_email from Customers
where cust_id in

(select cust_id from  Orders
where order_num in

(select order_num from OrderItems
where prod_id='BR01'))

1
2
3
4
5
6
7
8

# 94.返回每个顾客不同订单的总金额 (opens new window)

坑点：OrderItems.order_num = Orders.order_num需要考虑，2个订单是否完整

select
  cust_id, 
  (
      select sum( item_price*quantity ) as total_ordered
      from OrderItems
      where OrderItems.order_num = Orders.order_num
      group by order_num
   ) as total_ordered
from
  Orders
order by  total_ordered desc

1
2
3
4
5
6
7
8
9
10
11

# 95.从 Products 表中检索所有的产品名称以及对应的销售 (opens new window)

select prod_name,
(
select sum(quantity) as quant_sold
from OrderItems
where Products.prod_id=OrderItems.prod_id
group by prod_name
)as quant_sold
from Products

1
2
3
4
5
6
7
8

# 11.⭐️联结表

# 96.返回顾客名称和相关订单号 (opens new window)「内连接inner join」

内连接又称等值连接，使用 INNER JOIN 关键字。

select
    cust_name, order_num
from
    Customers
    inner join 
    Orders 
    using (cust_id) #这里用using， 其实等价on，然后两边的相等
order by
    cust_name, order_num

1
2
3
4
5
6
7
8
9
10

select
    cust_name, order_num
from
    Customers
    inner join 
    Orders 
    on Customers.cust_id=Orders.cust_id
order by
    cust_name, order_num

1
2
3
4
5
6
7
8
9
10

可以不明确使用 INNER JOIN，而使用普通查询并在 WHERE 中将两个表中要连接的列用等值方法连接起来

select
    cust_name, order_num
from
    Customers, Orders 
where Customers.cust_id=Orders.cust_id
order by
    cust_name, order_num

1
2
3
4
5
6
7
8

# 97.返回顾客名称和相关订单号以及每个订单的总价 (opens new window)⭐️「多个inner join」

select Customers.cust_name, Orders.order_num, 
    OrderItems.quantity * OrderItems.item_price as OrderTotal
from Customers , Orders  , OrderItems
where 
    Customers.cust_id=Orders.cust_id
and
    Orders.order_num=OrderItems.order_num
order by Customers.cust_name asc;

1
2
3
4
5
6
7
8

inner join⭐️

select
    Customers.cust_name,
    Orders.order_num,
    OrderItems.quantity * OrderItems.item_price as OrderTotal
from
    Customers
    inner join Orders on Customers.cust_id = Orders.cust_id  #⭐️
    inner join OrderItems on Orders.order_num = OrderItems.order_num  #⭐️多个inner join的写法
order by
    Customers.cust_name asc;

1
2
3
4
5
6
7
8
9
10

# 98.确定哪些订单购买了 prod_id 为 BR01 的产品 (opens new window)（二）

select
    cust_id,
    order_date
from
    Orders
where
    order_num in (
        select
            order_num
        from
            OrderItems
        where
            prod_id = 'BR01'
    )
order by
    order_date

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 99.返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件 (opens new window)（二）

# 100.确定最佳顾客的另一种方式 (opens new window)（二）

select distinct
    Customers.cust_name,
    sum(OrderItems.item_price * OrderItems.quantity) as total_price
from
    Orders
    inner join OrderItems on Orders.order_num = OrderItems.order_num #⭐️
    inner join Customers on Orders.cust_id = Customers.cust_id #⭐️多个inner join的写法
group by
    Customers.cust_name
having
    sum(OrderItems.item_price * OrderItems.quantity) >= 1000
order by
    total_price

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 12.⭐️创建高级联结

# 101.检索每个顾客的名称和所有的订单号 (opens new window)（一）

select C.cust_name,O.order_num
    from Customers C 
    inner join Orders O on O.cust_id=C.cust_id
    order by C.cust_name

1
2
3
4

# 102.检索每个顾客的名称和所有的订单号 (opens new window)（二）

SELECT
    cust_name,
    order_num
FROM
    Customers
    LEFT JOIN Orders USING (cust_id)
Order by
    cust_name

1
2
3
4
5
6
7
8
9

# 103.返回产品名称和与之相关的订单号 (opens new window)

select
    Products.prod_name,
    OrderItems.order_num
from
    Products
    left join OrderItems on Products.prod_id = OrderItems.prod_id
order by
    1 asc; #等价于，我们选出来的第1列排序，即Products.prod_name asc;

1
2
3
4
5
6
7
8
9

# 104.返回产品名称和每一项产品的总订单数 (opens new window)

select
    prod_name,
    count(order_num) as orders
from
    Products
    left join OrderItems using (prod_id)
group by
    prod_name
order by
    prod_name

1
2
3
4
5
6
7
8
9
10
11

# 105.列出供应商及其可供产品的数量 (opens new window)

select
    a.vend_id,
    count(b.prod_id) prod_id
from
    Vendors a
    left join Products b using (vend_id)
group by
    a.vend_id
order by
    a.vend_id asc

1
2
3
4
5
6
7
8
9
10

# 13.组合查询「union」

# 106.将两个 SELECT 语句结合起来 (opens new window)(一)「easy」⭐️

最后按产品 id 对结果进行升序排序。

select
  prod_id, quantity
from
  OrderItems
where
  quantity = 100

union

select
  prod_id, quantity
from
  OrderItems
where
  prod_id like 'BNBG%';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 107.将两个 SELECT 语句结合起来 (opens new window)(2)easy

select * from OrderItems 
where prod_id like 'BNBG%' 
union 
select * from OrderItems 
where quantity=100 order by prod_id

1
2
3
4
5

等价的

select prod_id, quantity
from OrderItems
 where quantity=100
or prod_id like 'BNBG%'
order by prod_id

1
2
3
4
5

# 108.组合 Products 表中的产品名称和 Customers 表中的顾客名称 (opens new window)「mid」⭐️

拼接cust_name和prod_name并根据结果升序排序

==Tips:「默认会去除相同行」，如果需要保留相同行，使用 union all==

select
    prod_name
from
    Products
union all   #核心
select
    cust_name
from
    Customers
order by
    prod_name

1
2
3
4
5
6
7
8
9
10
11

# 109.纠错4 (opens new window)easy

SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'MI' 

UNION 
SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'IL'
ORDER BY cust_name;

1
2
3
4
5
6
7
8
9

等价的

SELECT
    cust_name,
    cust_contact,
    cust_email
FROM
    Customers
WHERE
    cust_state = 'MI'
    OR cust_state = 'IL'
ORDER BY
    cust_name;

1
2
3
4
5
6
7
8
9
10
11
12

# ⭐️总结记忆⭐️

distinct(XXX)去重某列
从小到大，正序，asc
从大到小，逆序，desc

- 1内连接
  - 内连接（等值连接）AAA inner BBB  XX on
    -自连接，自连接可以看成内连接的一种，只是连接的表是自身而已。，
    「employee」 AS e1 INNER JOIN 「employee」 AS e2 ON e1.department = e2.department AND e2.name = "Jim";
    
    内连接和自然连接的区别：内连接提供连接的列，而自然连接自动连接所有同名列。
    
- 2\自然连接
  - aaa AS A natural join bbb AS B;
  
- 3\外连接  外连接保留了没有关联的那些行（OUTER JOIN）
  左外连接 Customers left outer join Orders on Customers.cust_id = Orders.cust_id
  	左外连接就是保留左表没有关联的行
  右外连接 RIGHT  OUTER  JOIN
  全外连接 FULL OUTER JOIN

- 4\组合查询,,,使用 UNION 来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为
M+N 行
	1\「每个查询」「必须」包含相同的列、表达式和聚集函数。
	2、「默认会去除相同行」，如果需要保留相同行，使用 union all
	3\只能包含一个 ORDER BY 子句，并且必须位于语句的最后。
	SELECT col FROM mytable WHERE col = 1
UNION SELECT col FROM mytable WHERE col =2;

= 5\交叉连接（CROSS JOIN）：有两种，显式的和隐式的


显式的交叉连接，使用CROSS JOIN。
SELECT O.ID, O.ORDER_NUMBER, C.ID,
C.NAME
FROM ORDERS as O 
cross join
CUSTOMERS as C
WHERE O.ID=1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

# SQL大厂面试真题

链接：https://www.nowcoder.com/exam/oj?page=1&tab=SQL%E7%AF%87&topicId=268 (opens new window)

# 1.某音短视频

# 156 各个视频的平均完播率

select
    a.video_id,
    round(
        sum(
            case
                when timestampdiff (second, b.start_time, b.end_time) >= a.duration then 1
                else 0
            end
        ) / count(b.uid),
        3
    ) as avg_com_play_rate
from
    tb_video_info a
    join tb_user_video_log b on a.video_id = b.video_id
where
    YEAR (b.start_time) = 2021
group by
    a.video_id
order by
    avg_com_play_rate desc

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

# 2.用户增长场景（某度信息流）

# 3.电商场景（某东商城）

# 4.出行场景（某滴打车）

# 5.某宝店铺分析（电商模式）

# 6.牛客直播课分析（在线教育行业）

# 7.某乎问答（内容行业）

# 190 某乎问答11月份日人均回答量

# 191 某乎问答高质量的回答中用户属于各级别的数量

# 192 某乎问答单日回答问题数大于等于3个的所有用户

# 193 某乎问答回答过教育类问题的用户里有多少用户回答过

# 194.某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级

# 面试

查询选课门数超过2门的学生的平均成绩和选课门数。

数据库中有三张表，student学生表、course选课表、sc成绩表。

Cno（课程号）,Cname,hours,Sno（学号）

select
    student.Sname, avg(sc.grade),  #
    count(sc.cno)  #选课门数，count，sc
from
    student,
    sc
where
    student.sno = sc.sno
group by
    student.Sname
having
    count(sc.cno) > 2

1
2
3
4
5
6
7
8
9
10
11
12
13

# 参考资料

《SQL必知必会》
- 还有人整理了：CyC2018的SQL语法 (opens new window)
《MySQL必知必会》
廖雪峰的SQL (opens new window)
自学SQL网，站点 (opens new window)『有实践题』
知乎上关于SQL练习的平台 (opens new window)
阿里巴巴，SQL代码编码原则和规范 (opens new window)
- 恒生电子，SQL编码规范2020