SQL-50

📕文章题目选自LeetCode力扣基础SQL50题
😊该文章内容基于作者本人思考总结，如有错误欢迎指正
🏠题单地址：https://leetcode.cn/studyplan/sql-free-50/

查询

可回收且低脂的产品-where and 语句的使用

where and 语句的使用

select product_id from Products
where low_fats = "Y" and recyclable = "Y";

584.寻找用户推荐人-不等于(<>)和判断为NULL(is null)的使用

不等于(<>)和判断为NULL(is null)的使用

select name from customer
where referee_id <> 2 or referee_id is NULL;

595.大的国家-大于等于(>=)的使用

大于等于(>=)的使用

select name,population,area
from World
where World.population >= 25000000 or World.area >= 3000000;

1148.文章浏览I-去重(distinct)的使用

去重(distinct)的使用

select DISTINCT author_id as id
from Views 
where author_id = viewer_id
order by id;

1683.无效的推文-CHAR_LENGTH

求单列字符长度的使用

select tweet_id
from tweets
where CHAR_LENGTH(content) > 15;

连接

1378. 使用唯一标识码替换员工ID-左连接

select unique_id,name 
from Employees left join EmployeeUNI
on Employees.id = EmployeeUNI.id;

1068. 产品销售分析 I-左连接

select product_name,year,price
from Sales left join Product
on Sales.product_id = Product.product_id;

1581. 进店却未进行过交易的顾客-左连接+GROUP

select customer_id,count(customer_id) as count_no_trans
from Visits left join Transactions
on Visits.visit_id = Transactions.visit_id
where transaction_id is NULL
Group by customer_id;

197. 上升的温度-datediff+cross join连接

CROSS JOIN 会产生两个表的笛卡尔积，即第一个表的每一行与第二个表的每一行进行组合。
例如，如果表 A 有 m 行，表 B 有 n 行，那么 CROSS JOIN 连接后的结果将有 m×n 行。
```
select a.id
from Weather a join Weather b
on a.Temperature > b.Temperature
and datediff(a.recordDate, b.recordDate) =1;
```
1661. 每台机器的进程平均运行时间-CASE+小数ROUND+COUNT+SUM

除数：SUM(CASE WHEN activity_type = ‘end’ THEN timestamp ELSE -timestamp END)
被除数：COUNT(DISTINCT(process_id))
小数点：ROUND(x,?)
条件判断：CASE WHEN activity_type = ‘end’ THEN timestamp ELSE -timestamp END
```
select machine_id,
ROUND(SUM(CASE WHEN activity_type = 'end' THEN timestamp ELSE -timestamp END)/
COUNT(DISTINCT(process_id)),3) as processing_time
from activity
group by machine_id;
```

577.员工奖金-左右连接+NULL判断

select name,b.bonus
from Bonus as b right join Employee as e 
on b.empId = e.empId
where b.bonus IS NULL or b.bonus < 1000;

1280. 学生们参加各科测试的次数-join连接+子查询

先找出每一个学生对应的参与每一个科目的次数再左右连接

SELECT student_id, subject_name, COUNT(*) AS attended_exams
FROM Examinations
GROUP BY student_id, subject_name

SELECT 
    s.student_id, s.student_name, sub.subject_name, IFNULL(grouped.attended_exams, 0) AS attended_exams
FROM 
    Students s
CROSS JOIN 
    Subjects sub
LEFT JOIN (
    SELECT student_id, subject_name, COUNT(*) AS attended_exams
    FROM Examinations
    GROUP BY student_id, subject_name
) grouped 
ON s.student_id = grouped.student_id AND sub.subject_name = grouped.subject_name
ORDER BY s.student_id, sub.subject_name;

570. 至少有5名直接下属的经理-HAVING

WHERE 子句在分组之前执行，用于筛选原始数据集中的行。
HAVING 子句在分组之后执行，用于筛选分组后的结果集。
```
select Manager.Name as Name
from
Employee as Manager join Employee as Report
on Manager.Id = Report.ManagerId
group by Manager.Id
having count(Report.Id) >= 5
```
1934. 确认率-AVG+ROUND+左右连接+IFNULL替换

语句拆分ROUND(IFNULL(AVG(c.action=’confirmed’),0),2)
AVG(c.action='confirmed')计算满足条件c.action='confirmed'的平均值。这里实际上是判断action列的值是否等于'confirmed'，如果等于则视为满足条件，在计算平均值时会将满足条件的行视为1，不满足条件的行视为0，然后求这些值的平均值。
IFNULL(...)表示如果前面的平均值计算结果为NULL，则将其替换为0。这是为了处理当没有满足条件的行时，避免出现NULL结果。
```
select s.user_id,
ROUND(IFNULL(AVG(c.action='confirmed'),0),2) as confirmation_rate
from Signups as s left join Confirmations c
on s.user_id = c.user_id
group by s.user_id;
```

聚合函数

620. 有趣的电影-MOD

select * from cinema
where description <> 'boring' and mod(id, 2) = 1
order by rating desc;

1251.❗平均售价-聚合函数+左右连接+BETWEEN

SELECT
    product_id,
    IFNULL(Round(SUM(sales) / SUM(units), 2), 0) AS average_price
FROM (
    SELECT
        Prices.product_id AS product_id,
        Prices.price * UnitsSold.units AS sales,
        UnitsSold.units AS units
    FROM Prices 
    LEFT JOIN UnitsSold ON Prices.product_id = UnitsSold.product_id
    AND (UnitsSold.purchase_date BETWEEN Prices.start_date AND Prices.end_date)
) T
GROUP BY product_id

1075.项目员工I-AVG

select project_id,ROUND(AVG(experience_years),2) as average_years 
from Project as p left join Employee as e
on p.employee_id = e.employee_id
group by project_id;

1211. 查询结果的质量和占比-AVG+COUNT+SUM+ROUND+IF+/

SELECT 
    query_name, 
    ROUND(AVG(rating/position), 2) quality,
    ROUND(SUM(IF(rating < 3, 1, 0)) * 100 / COUNT(*), 2) poor_query_percentage
FROM Queries
Where query_name IS NOT NULL
GROUP BY query_name

1633. 各赛事的用户注册率-ROUND+SUM+COUNT

select contest_id,ROUND(COUNT(u.user_id) * 100/(select count(*) from users),2) as percentage
from Users as u left join Register as r
on u.user_id = r.user_id
where contest_id IS NOT NULL
group by contest_id
order by percentage desc,contest_id asc;

1193. ❗每月交易 I-COUNT+SUM+IF+GROUP+DATE_FORMAT

select 
DATE_FORMAT(trans_date, '%Y-%m') as month,
country,
count(*) as trans_count,
COUNT(IF(state = 'approved', 1, NULL)) as approved_count,
SUM(amount) as trans_total_amount,
SUM(IF(state = 'approved', amount, 0)) as approved_total_amount
from Transactions
group by month,country;

1174. 即时食物配送 II-MIN+SUM+子查询

SUM(order_date = customer_pref_delivery_date) 即可查询到满足条件的数量
MIN找到最小日期

select ROUND(
    SUM(order_date = customer_pref_delivery_date) * 100 /
    count(*),
    2) as immediate_percentage
from Delivery
where (customer_id, order_date) in (
    select customer_id, min(order_date)
    from delivery
    group by customer_id
)

550. 游戏玩法分析 IV-SUM+COUNT+IFNULL+DATE_ADD+MIN

1.先将日期添加为新的一天创建新表
2.将旧表的结束日期与新表比对找到符合条件的一天
3.IFNULL去掉NULL
4.数学除法

select IFNULL(round(count(distinct(Result.player_id)) / count(distinct(Activity.player_id)), 2), 0) as fraction
from (
  select Activity.player_id as player_id
  from (
    select player_id, DATE_ADD(MIN(event_date), INTERVAL 1 DAY) as second_date
    from Activity
    group by player_id
  ) as Expected, Activity
  where Activity.event_date = Expected.second_date and Activity.player_id = Expected.player_id
) as Result, Activity

排序和分组

2356. ❗每位教师所教授的科目种类的数量-COUNT+DISTINCT

select teacher_id,COUNT(DISTINCT subject_id) as cnt
from teacher
group by teacher_id

1141. 查询近30天活跃用户数-DATEDIFF+COUNT

SELECT activity_date AS day, count(DISTINCT user_id) AS active_users
FROM Activity
WHERE DATEDIFF("2019-07-27",activity_date) BETWEEN 0 AND 29
GROUP BY activity_date

1084. ❗销售分析III-BETWEENAND

分组后的数据进行比对
or null表示如果前面的条件不满足或者结果为NULL，也会被考虑在统计范围内。这样的写法可能是为了确保即使某些行的sale_date为NULL也能被计入总数中

# 在这个时间内售出的商品数量等于总商品数量
select p.product_id,p.product_name
from Product as p left join Sales as s
on p.product_id = s.product_id
group by p.product_id
having count(sale_date between '2019-01-01' and '2019-03-31' or null) = count(*)

596. 超过 5 名学生的课-HAVING

select class from Courses
group by class
having count(class) >= 5
;

1729. 求关注者的数量-GROUPBY

select user_id,count(user_id) as followers_count
from Followers
group by user_id
order by user_id;

619. 只出现一次的最大数字-GROUPBY+MAX

select max(num) as num
from (select num
    from MyNumbers
    group by num
    having count(num) = 1) as t
;

1045. 买下所有产品的客户-GROUPBY+COUNT

select customer_id
from Customer
where product_key IN (SELECT product_key FROM Product)
GROUP BY customer_id
HAVING COUNT(DISTINCT product_key) = (SELECT COUNT(*) FROM Product);

高级查询和连接

对同一列数据进行筛选和计算即需要用到自链接

1731. ❗❗每位经理的下属员工数量-聚合函数+自连接

select a.employee_id as 'employee_id',a.name as 'name',count(b.employee_id) as 'reports_count',round(avg(b.age), 0) as 'average_age'
from Employees a join Employees as b
on a.employee_id = b.reports_to
group by a.employee_id
order by a.employee_id

1789. 员工的直属部门-UNION

select employee_id,department_id
from Employee
group by employee_id
having count(Employee.employee_id) = 1
UNION
select  employee_id, department_id
FROM
    Employee
WHERE
    primary_flag = 'Y' ;

610. 判断三角形-CASE END+条件判断

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;

180. 连续出现的数字-DISTINCT

SELECT *
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
;

1164. ❗❗指定日期的产品价格-连接+IFNULL+条件判断

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

1204. 最后一个能进入巴士的人-GROUPBY+HAVING

select a.person_name
from Queue a, Queue b
where a.turn >= b.turn 
group by a.person_id having sum(b.weight) <= 1000
order by a.turn desc
limit 1;

1907. 按分类统计薪水-UNION+CASE WHEN END

SELECT 
    'Low Salary' AS category,
    SUM(CASE WHEN income < 20000 THEN 1 ELSE 0 END) AS accounts_count
FROM 
    Accounts

UNION
SELECT  
    'Average Salary' category,
    SUM(CASE WHEN income >= 20000 AND income <= 50000 THEN 1 ELSE 0 END) 
    AS accounts_count
FROM 
    Accounts

UNION
SELECT 
    'High Salary' category,
    SUM(CASE WHEN income > 50000 THEN 1 ELSE 0 END) AS accounts_count
FROM 
    Accounts

子查询

1978. 上级经理已离职的公司员工-左连接

select e1.employee_id 
from Employees e1 left join Employees e2
on e1.manager_id = e2.employee_id 
where e1.salary < 30000 and e1.manager_id is not null and e2.employee_id is null
order by e1.employee_id

626. ❗换座位-CASEWHEN逻辑判断

select (
    case 
    when id%2=0 then id-1
    when id%2=1 and id < (select max(id) from Seat) then id + 1
    else id
    end
)as id ,student from Seat
order by id;

1341. 电影评分-左右连接

(SELECT u.name AS results
FROM Users u
LEFT JOIN MovieRating mr ON u.user_id = mr.user_id
GROUP BY u.user_id
ORDER BY COUNT(*) DESC, u.name ASC
LIMIT 1)

UNION ALL

(SELECT m.title AS results
FROM Movies m
LEFT JOIN MovieRating mr ON m.movie_id = mr.movie_id AND YEAR(mr.created_at) = 2020 AND MONTH(mr.created_at) = 2
GROUP BY mr.movie_id
ORDER BY AVG(mr.rating) DESC, m.title
LIMIT 1);

1321. ❗❗餐馆营业额变化增长-窗口函数

[你要的操作] OVER ( PARTITION BY  <用于分组的列名>
                    ORDER BY <按序叠加的列名> 
                    ROWS <窗口滑动的数据范围> )

当前行 - current row
之前的行 - preceding
之后的行 - following
无界限 - unbounded
表示从前面的起点 - unbounded preceding
表示到后面的终点 - unbounded following

求出窗口的大小

SUM(amount) OVER ( ORDER BY visited_on ROWS 6 PRECEDING ) AS sum_amount

-- 求得出的窗口的平均值
SELECT DISTINCT visited_on,
       sum_amount AS amount, 
       ROUND(sum_amount/7, 2) AS average_amount
FROM (
    SELECT visited_on, 
       SUM(amount) OVER ( ORDER BY visited_on ROWS 6 PRECEDING ) AS sum_amount
    FROM (
        -- 求出每日消费的当量
        SELECT visited_on, 
            SUM(amount) AS amount
        FROM Customer
        GROUP BY visited_on
         ) TT
     ) LL
    --  求出六天之内的差值
WHERE DATEDIFF(visited_on, (SELECT MIN(visited_on) FROM Customer)) >= 6

602. 好友申请 II ：谁有最多的好友-UNIONALL

UNION ALL：同样用于合并多个 SELECT 语句的结果集，但不会去除重复行。

UNION：用于合并两个或多个 SELECT 语句的结果集，并去除重复行。

select t1.ids as id,count(*) as num
from (
select requester_id as ids from RequestAccepted 
union all
select accepter_id as ids from RequestAccepted
)as t1
group by id
order by num desc
limit 1;

585. 2016年的投资-CONCAT+HAVING

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
)
AND CONCAT(LAT, LON) IN
(
  SELECT
    CONCAT(LAT, LON)
  FROM
    insurance
  GROUP BY LAT , LON
  HAVING COUNT(*) = 1
)
;

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

SELECT
d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
Employee e1
    JOIN
Department d ON e1.DepartmentId = d.Id
WHERE
3 > (SELECT
        COUNT(DISTINCT e2.Salary)
    FROM
        Employee e2
    WHERE
        e2.Salary > e1.Salary
            AND e1.DepartmentId = e2.DepartmentId
    )
;

高级字符串函数 / 正则表达式 / 子句

1667. 修复表中的名字-LEFT+RIGHT+CONCAT+UPPER+LOWER

select user_id,concat(upper(left(name,1)),lower(right(name,length(name)-1))) as name
from users
order by user_id;

1527. 患某种疾病的患者

正则表达式
^：表示一个字符串或行的开头
[a-z]：表示一个字符范围，匹配从 a 到 z 的任何字符。
[0-9]：表示一个字符范围，匹配从 0 到 9 的任何字符。
[a-zA-Z]：这个变量匹配从 a 到 z 或 A 到 Z 的任何字符。请注意，你可以在方括号内指定的字符范围的数量没有限制，您可以添加想要匹配的其他字符或范围。
[^a-z]：这个变量匹配不在 a 到 z 范围内的任何字符。请注意，字符 ^ 用来否定字符范围，它在方括号内的含义与它的方括号外表示开始的含义不同。
[a-z]*：表示一个字符范围，匹配从 a 到 z 的任何字符 0 次或多次。
[a-z]+：表示一个字符范围，匹配从 a 到 z 的任何字符 1 次或多次。
.：匹配任意一个字符。
\.：表示句点字符。请注意，反斜杠用于转义句点字符，因为句点字符在正则表达式中具有特殊含义。还要注意，在许多语言中，你需要转义反斜杠本身，因此需要使用\\.。
$：表示一个字符串或行的结尾。

select patient_id,patient_name,conditions
from Patients
where conditions regexp '\\bDIAB1.*';

“\b” 是单词边界，表示匹配的位置必须是在一个单词的开头或结尾处。
“DIAB1” 是固定的字符序列，会严格匹配这几个字符。
“.*” 表示匹配任意数量的除换行符之外的任何字符。

196. 删除重复的电子邮箱-DELETE

delete p1 from Person p1,
Person p2
where p1.Email = p2.Email AND p1.ID > p2.ID;

176. 第二高的薪水-OFFSET

使用limit 1 offset 1跳过第一行取第二行

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

1484. 按日期分组销售产品-SEPARATOR+GROUP_CONCAT

GROUP_CONCAT是一个聚合函数，它将分组中的某一列的值连接成一个字符串。
SEPARATOR是GROUP_CONCAT函数的一个参数，用于指定连接字符串时的分隔符。

SELECT 
    sell_date,
    count(distinct(product)) as num_sold,
    GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ',') AS products
FROM 
    Activities
GROUP BY 
    sell_date
ORDER BY 
    sell_date ASC

1327. 列出指定时间段内所有的下单产品-GROUPBY

select product_name,sum(unit) as unit
from Products as p right join Orders as o
on p.product_id = o.product_id
where month(order_date) = 2 and year(order_date) = 2020
group by product_name
having sum(unit) >= 100

1517. 查找拥有有效邮箱的用户

SELECT user_id, name, mail
FROM Users
WHERE mail REGEXP '^[a-zA-Z][a-zA-Z0-9_.-]*\\@leetcode\\.com$';

^[a-zA-Z] 表示开头取一个字符
[a-zA-Z0-9_.-]* 指包含a-zA-Z0-9_.-的0个或者多个字符
\\@ 表示转义字符@
\\. 表示转义字符'.'