【SQL基础】【leetcode】SQL50题

查询

（1）可回收且低脂的产品

题目链接

SELECT product_id
FROM Products
WHERE low_fats='Y' AND recyclable='Y';

很简单，最基础的sql语句。

（2）寻找用户推荐人

题目链接

SELECT name
FROM Customer
WHERE referee_id != 2 OR referee_id is NULL

SQL中对于空值的处理是is NULL 或者 is NOT NULL，对于布尔逻辑来说，null值不是true也不是false，因此null值和任何值比较结果都是UNKNOWN。为了解决这种unknown的情况，SQL使用了is NULL和is NOT NULL。

（3）大的国家

题目链接

SELECT name,population,area
FROM World 
WHERE population>=25000000 OR area>=3000000

即对OR的使用，可以把两个条件放在一起。

（4） 文章浏览1

题目描述

SELECT distinct(author_id) as id
FROM Views
WHERE author_id=viewer_id
ORDER BY id

因为可能出现不止浏览一次的情况，因此要使用distinct进行去重，同时注意返回顺序。（逆序为DESC为逆序）

（5）无效的推文

题目描述

SELECT tweet_id
FROM tweets
WHERE LENGTH(content) > 15

使用LENGTH函数即可解决。在 MySQL 中，LENGTH 返回字符串的字节长度，而不是字符数量。因此对于多字节字符集时，如 UTF-8，用LENGTH去处理占用多个字节的字符（例如中文），就会出现问题。因为一个中文字符占3字节。所以你可以使用CHAR_LENGTH来处理

连接

(1）使用唯一标识码替换员工ID

题目描述

SELECT unique_id, name
FROM Employees
LEFT JOIN EmployeeUNI
USING(id)

就是基础的左连接，如果有疑问可以看介绍。

左连接就是Employees根据id的进行链接EmployeeUNI，也就是一一对应。如果出现EmployeeUNI里没有能对应Employees的内容，那就只显示Employees里的内容。

（2）产品销售分析 I

题目描述

SELECT product_name, year, price
FROM Sales s, Product p
WHERE s.product_id=p.product_id

不需要特殊的连接方式，直接用inner join就可以。或者我写的这种隐式链接。
这一题的本质就是将两个表合并，得到所需的信息，因此不需要额外的操作了。

（3）进店却未进行过交易的顾客

题目描述

有两种做法。

# Write your MySQL query statement below

SELECT customer_id, COUNT(v.visit_id) as count_no_trans
FROM Visits v
WHERE v.visit_id NOT IN (SELECT visit_id
                         FROM Transactions)
GROUP BY customer_id
ORDER BY count_no_trans

首先是子查询，用子查询查出Transaction的全部visit_id，然后主查询的部分查找visit_id不在Transaction里的，就可以得到只光顾商店的客人。然后对于计算次数，可以用count聚合函数进行计算，但需要用group by进行分组，这是因为如果不这样使用，聚合函数返回的结果是一个值，需要用分组进行分开。这样的问题在这里也出现过。

第二种做法是只使用联表查询：

select customer_id, count(customer_id) as count_no_trans
from visits
left join transactions using(visit_id)
where transaction_id is null
group by customer_id;

这里用LEFT JOIN后，得到了每个用户的transaction次数，但有些用户是没有交易（买东西）的，因此在where的地方要判断transaction_id是否为空，筛选出的结果就是未光顾的。

注意，联表查询得到的结果是一张临时表，而最基础的join（inner）则是不会出现NULL，对于本题是只保留1、2、5的用户，而LEFT JOIN则可以保留多的一方的数据，得出NULL值。

(4)上升的温度

题目描述

SELECT w2.id
FROM Weather w1, Weather w2
WHERE datediff(w2.recordDate, w1.recordDate)=1 AND w2.Temperature > w1.Temperature

DATE_DIFF 是一个用于计算两个日期之间差异的函数，它返回两个日期之间的差异，以天数为单位。
本题就是后一天温度大于前一天即可，因此我们返回的应该是w2的内容。

顺便一提，这样没有确定联表条件（例如w1.id=w2.id）的情况查询出的内容是笛卡儿积，而WHERE的限制条件则是对其的筛选。得到的结果一定是温度上w2>w1但日期只差一天。因此如果SELECT的是w1的id，则会出现相反的结果。

(5)每台机器的进程平均运行时间

题目链接

SELECT s.machine_id, ROUND(AVG(e.timestamp-s.timestamp), 3) as processing_time
FROM (SELECT machine_id, process_id, timestamp
    FROM Activity
    WHERE activity_type='start') as s,
    (SELECT machine_id, process_id, timestamp
    FROM Activity
    WHERE activity_type='end') as e
WHERE s.machine_id=e.machine_id AND s.process_id=e.process_id
GROUP BY machine_id

可以把原始的表分为start表和end表，再进行联表查询，这样就可以计算时间戳的插值，进而使用AVG计算平均值，用ROUND保留三位小数。

（6）员工奖金

题目描述

SELECT name, bonus
FROM Employee
LEFT JOIN Bonus USING(empId)
WHERE bonus<1000 OR bonus IS NULL

如果要搜出NULL值，使用左连接，同时注意NULL值需要单独判断。

(7) 学生们参加各科测试的次数

题目链接

SELECT s.student_id, s.student_name, sub.subject_name,COUNT(e.subject_name) as attended_exams
FROM Students s
JOIN Subjects sub
LEFT JOIN Examinations e
ON e.student_id=s.student_id AND e.subject_name=sub.subject_name
GROUP BY student_id, subject_name
ORDER BY student_id

这个题最大的难点就是确保每个学生都有三个科目，即便他们没参加考试。但对于题目观察一下就可以知道，可以直接对学生表和科目表进行笛卡儿积的操作，这样就可以确保每个学生都有三个科目，再将结果进行左连接，即可确保结果是每个学生且都有三个科目。

（8）至少有5名直接下属的经理

题目链接

SELECT name
FROM (SELECT COUNT(managerId) as num,managerId
    FROM Employee
    GROUP BY managerId
    HAVING num>=5) as t, Employee e
WHERE e.id=t.managerId

从Employee中搜索出managerID数量大于5的managerID，同时由managerID分组。再和Employee进行联表查询即可。

（9）确认率

题目描述

SELECT t1.user_id, IFNULL(ROUND((t2.num/t1.num), 2), 0) as confirmation_rate
FROM(SELECT user_id, COALESCE(t.num, 0) as num
    FROM(SELECT COUNT(user_id) as num,user_id
        FROM Confirmations
        GROUP BY user_id) as t
    RIGHT JOIN Signups USING(user_id)) as t1,

    (SELECT user_id, COALESCE(t.num, 0) as num
    FROM(SELECT COUNT(user_id) as num,user_id
        FROM Confirmations
        RIGHT JOIN Signups USING(user_id)
        WHERE action='confirmed'
        GROUP BY user_id) as t
    RIGHT JOIN Signups USING(user_id)) as t2
WHERE t1.user_id=t2.user_id
GROUP BY t2.user_id

重点之一是用IFNULL函数进行去NULL.
用两个子查询查出总数和timeout，进行除法计算。

聚合函数

（1）有趣的电影

题目描述

SELECT *
FROM cinema
WHERE (id%2!=0) AND description!='boring'
ORDER BY rating DESC

很简单 注意降序排列即可。

（2）平均售价

题目描述

SELECT 
    p.product_id, 
    IFNULL(ROUND(COALESCE(SUM(p.price * u.units), 0) / IFNULL(SUM(u.units), 0), 2), 0) AS average_price
FROM 
    Prices p
LEFT JOIN 
    UnitsSold u ON p.product_id = u.product_id AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY 
    p.product_id;

联表查询将两个表组合成一个，用时间约束价格对应的数量，然后就是如何把每列的价格和数量相乘，再将每种产品的值加和除以总数量。

对于剩下的操作，就是使用聚合函数进行计算。IFNULL可以处理null值。

（3）项目员工

题目链接

SELECT project_id, ROUND((SUM(experience_years)/COUNT(e.employee_id)), 2) as average_years
FROM Project p, Employee e
WHERE p.employee_id=e.employee_id
GROUP BY project_id

思路和上一题类似，联表后计算数值，用GROUP BY来进行分组。一般设计聚合函数都要分组。

（4）各赛事的用户注册率

题目描述

SELECT contest_id,ROUND((COUNT(user_id)/(select count(user_id) from users)), 4)*100 as percentage
FROM Register
GROUP BY contest_id
ORDER BY percentage DESC, contest_id ASC;

可以不用联表查询，因为分母实际上就是一个常数，即用户的数量。 因此直接对另一个表分组再计算每一组的个数即可。

(5)查询结果的质量和占比

题目链接

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

这题的聚合函数稍微复杂一点，quality比较简单，而poor_query_percentage需要用上if来判断是否小于3。

(6) 每月交易1

题目链接

# Write your MySQL query statement below
SELECT month, country, (COUNT(id)) as trans_count, (SUM(IF(state="approved", 1, 0))) as approved_count, (SUM(amount)) as trans_total_amount, (SUM(IF(state="approved", amount, 0))) as approved_total_amount
FROM (SELECT id, country, state, amount, DATE_FORMAT(trans_date, '%Y-%m') AS month
      FROM Transactions
    ) tmp
GROUP BY country,  month

和上面一样，重点在于SUM和IF函数的使用。

（7） 即时食物配送 II

题目描述

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
)

注意，如果在子查询里得到customer_pref_delivery_date,会出现数据对不上的情况。因为min(order_date)选出的内容不一定对应customer_pref_delivery_date, 因此得到的答案未必正确。

（8） 游戏玩法分析 IV

题目链接

SELECT ROUND(COUNT(t1.player_id)/(SELECT COUNT(distinct(player_id)) FROM Activity ), 2) as fraction
FROM (SELECT player_id, MIN(event_date) as date
      FROM Activity
      GROUP BY player_id) t1,
      Activity t2
WHERE t1.player_id=t2.player_id AND datediff(t2.event_date, t1.date)=1 

分母用子查询得到常数即可。

排序和分组

（1） 每位教师所教授的科目种类的数量

题目链接

SELECT teacher_id, COUNT(distinct(subject_id)) as cnt
FROM Teacher
GROUP BY teacher_id

用distinct关键字和COUNT函数计算出每个教师包含了多少个不同的科目，即可得到答案。

(2) 查询近30天活跃用户数

题目链接

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

注意日期表达需要加上单引号。如果不用BETWEEN AND的写法，也可以只用一个AND，多用一个datediff即可。

（3）销售分析III

题目链接

SELECT distinct(s.product_id), product_name
FROM Sales s LEFT JOIN Product p USING(product_id)
GROUP BY product_id
HAVING count(sale_date BETWEEN '2019-01-01' AND '2019-03-31' or null) = COUNT(*)

分好组后用having进行筛选，如果春季内的数量等于全部的数量则可以保留。

（4）超过 5 名学生的课

题目描述

SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(DISTINCT(student))>=5

先分组再进行筛选，否则WHERE是早于分组的，会先筛选再分组，这对聚合函数来说会出错。

（5）求关注者的数量

添加链接描述

SELECT user_id, COUNT(follower_id) as followers_count
FROM Followers
GROUP BY user_id
ORDER BY user_id

很简单

（6）只出现一次的最大数字

题目描述

SELECT MAX(num) as num
FROM (SELECT num
    FROM MYNumbers
    GROUP BY num
    HAVING COUNT(num)=1
    ORDER BY num DESC) t

也很简单，虽然效率不高。

（7）买下所有产品的客户

题目链接

SELECT customer_id 
FROM Customer c,Product p
GROUP BY customer_id
HAVING COUNT(distinct(c.product_key))=COUNT(distinct(p.product_key))

很简单，在HAVING里进行筛选即可。

高级查询和连接

(1)每位经理的下属员工数量

题目链接

SELECT employee_id, name, reports_count, average_age
FROM Employees e, 
    (SELECT reports_to, ROUND(AVG(age), 0) as average_age, COUNT(employee_id) as reports_count
    FROM Employees 
    GROUP BY reports_to) as t  
WHERE e.employee_id=t.reports_to
ORDER BY employee_id

在子查询中可以很简单的得到平均年龄和人数，以及汇报的对象。再用Employees表与子表进行联表查询则可以得到领导的信息。

（2）员工的直属部门

题目链接

SELECT employee_id, department_id
FROM Employee e
WHERE primary_flag='Y'
GROUP BY employee_id
UNION
SELECT employee_id, department_id
FROM Employee
GROUP BY employee_id
HAVING COUNT(department_id)=1

直接查询两个结果进行UNION即可，因为两个结果集合的交集为空，而并集就是最终的结果。

（3）判断三角形

题目链接

SELECT x, y, z, IF((x+y)>z AND (x+z)>y AND (y+z)>x, 'Yes', 'No') as triangle
FROM Triangle

非常简单，只需要在SELECT里进行筛选即可。

(4) 连续出现的数字

题目链接

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
;

很无聊的题，没什么实际意义。

（5）指定日期的产品价格

题目链接

SELECT t1.product_id, t2.new_price as price
FROM     
    (SELECT product_id, MAX(change_date) as change_date
    FROM Products
    GROUP BY product_id) as t1,
    Products t2
WHERE t1.product_id=t2.product_id AND t1.change_date=t2.change_date

审题要注意，题目要求是在2019-08-16以及之前的…
正确答案如下：

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

（6）最后一个进巴士的人

题目描述

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

没什么意义，业务逻辑放在SQL里属于自讨苦吃。

(7)按分类统计薪水

题目链接

SELECT 
    categories.category,
    IFNULL(t.accounts_count, 0) as accounts_count
FROM 
    (SELECT 'Low Salary' AS category UNION ALL SELECT 'Average Salary' UNION ALL SELECT 'High Salary') categories
LEFT JOIN 
    (SELECT 
        IF(income<20000, 'Low Salary', IF(income<=50000, "Average Salary", "High Salary")) as category, 
        COUNT(income) as accounts_count
    FROM Accounts
    GROUP BY category
    ) as t ON categories.category=t.category
GROUP BY 
    categories.category;

如果不用考虑空值那就只需要一个搜索即可。但题目是需要考虑空值，因此单独建立一个表，用SELECT “” AS UNION 来构造表，并且左连接简单的查询即可。最后用IFNULL来处理空值。

子查询

（1）上级经理已离职的公司员工

SElECT employee_id
FROM Employees
WHERE salary < 30000 AND manager_id NOT IN (
    SELECT employee_id
    FROM Employees
)
ORDER BY employee_id

主要只有两个条件，一个是薪水，用WHERE很轻松解决，而另一个就是上司已离职，这点用NOT IN+子查询即可。注意用NOT IN要单独加一个搜素，搜出一列数据，才能查找是否存在在这一列内。

（2）换座位

题目描述

SELECT 
    IF(id%2=1, IF(id!=cnt, id+1, id), id-1) as id, student
FROM 
    Seat s,
    (
        SELECT MAX(id) as cnt
        FROM Seat
    ) t
ORDER BY id

比较麻烦，也是比较烦的把后端的问题放到SQL里处理。

如果用后端写代码（例如JAVA，可以自主的控制遍历）的思想其实不太容易处理。 但如果只按照SQL的想法就会好一些， 可以把id进行判断，如果为奇数就+1，反之-1，最后按照id排序就可以调整顺序（因为排序是按照一整行进行更换）。 同时要注意一下当id为最大值时要进行特判。这里的特判用聚合函数会出问题，所以直接用子查询即可。

（3）电影评分

题目描述

(
    SELECT name AS results
    FROM Users u
    JOIN MovieRating mr ON u.user_id = mr.user_id
    GROUP BY u.user_id, name
    ORDER BY COUNT(rating) DESC, name ASC
    LIMIT 1
)
UNION ALL
(
    SELECT title AS results
    FROM Movies m
    JOIN MovieRating mr ON m.movie_id = mr.movie_id
    WHERE mr.created_at LIKE '2020-02%'
    GROUP BY m.movie_id, title
    ORDER BY AVG(rating) DESC, title ASC
    LIMIT 1
)

这是两个独立的结果，所以可以直接查询并且UNION ALL即可。一定用UNION ALL的原因是样例里会出现人名和电影名一样的情况… 直接UNION会出现只有一行的情况。

（3）好友申请 II ：谁有最多的好友

题目描述

# Write your MySQL query statement below


SELECT 
    IF(t1.accepter_id=t2.requester_id, t1.accepter_id, IF(t1.num>t2.num, t1.accepter_id, t2.requester_id)) as id, 
    IF(t1.accepter_id=t2.requester_id, t1.num+t2.num, IF(t1.num>t2.num, t1.num, t2.num)) as num
FROM 
    (
        SELECT accepter_id, COUNT(*) as num
        FROM RequestAccepted
        GROUP BY accepter_id
        ORDER BY accepter_id
    ) as t1,
    (
        SELECT requester_id, COUNT(*) as num
        FROM RequestAccepted 
        GROUP BY requester_id
        ORDER BY requester_id
    ) as t2
-- WHERE t1.accepter_id=t2.requester_id
ORDER BY num DESC
LIMIT 1

坑点比较多，首先是联表之后，要在id相同的情况下计算和，这一定是最大的，如果id不同，就要看是不是accepter_id的最大值大还是requester_id的最大值大，然后用一样的逻辑取id。

（4）2016年的投资

题目描述

SELECT ROUND(SUM(tiv_2016),2) tiv_2016
FROM Insurance
WHERE (lat,lon) IN
(
    SELECT lat,lon
    FROM Insurance
    GROUP BY lat,lon
    HAVING COUNT(*) = 1
) AND
pid IN
(
    SELECT DISTINCT i1.pid
    FROM Insurance i1 JOIN Insurance i2
    ON i1.tiv_2015 = i2.tiv_2015 AND i1.pid <> i2.pid 
)

把选择的条件放在WHERE里更好，

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

（1）修复表中的名字

题目链接

SELECT user_id, CONCAT(UPPER(SUBSTRING(name, 1, 1)), LOWER(SUBSTRING(name, 2))) as name
FROM Users
ORDER BY user_id

就是最基本的对字符串操作，详见：

（2） 患某种疾病的患者

题目链接

SELECT patient_id, patient_name, conditions
FROM Patients
WHERE conditions LIKE "% DIAB1%" OR conditions LIKE "DIAB1%"

直接正则即可， 注意条件并列的写法。

（3）删除重复的电子邮箱

题目描述

DELETE p1 FROM Person p1, Person p2
WHERE p1.id > p2.id AND p1.email = p2.email;

即简单的删除语句，并且用联表查询选出id大且email重复的行删除。

（4）第二高的薪水

题目链接

SELECT IFNULL((
    SELECT distinct salary
    FROM Employee
    ORDER BY salary DESC
    LIMIT 1 OFFSET 1
), NULL) as SecondHighestSalary

用IFNULL判断是否为NULL。 distinct则用来判断重复情况，例如有两个人的薪水都是100，则也没第二高的。

(5)按日期分组销售产品

题目描述

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

GROUP_CONCAT 是MySQL中一个非常有用的聚合函数，用于将来自一个分组中的值连接成一个字符串。这个函数返回的值是每个分组中所有值连接后的结果

以下是GROUP_CONCAT函数的基本语法：

GROUP_CONCAT(expression [ORDER BY expression] SEPARATOR separator)

expression: 要连接的列表达式。
ORDER BY expression (可选): 指定如何排序连接的值。
SEPARATOR separator (可选): 指定值之间的分隔符。

(5) 列出指定时间段内所有的下单产品

t题目链接

SELECT product_name, unit
FROM (
        SELECT product_name, SUM(unit) as unit
        FROM Orders o, Products p
        WHERE o.product_id=p.product_id AND order_date BETWEEN "2020-02-01" AND "2020-02-29"
        GROUP BY product_name
    ) as t
WHERE unit>=100

很简单，子查询查出所需条件，再进行判断数量。

（6）查找拥有有效邮箱的用户

题目描述

SELECT *
FROM Users
WHERE mail REGEXP "^[a-zA-Z][a-zA-Z0-9_.-]*@leetcode\\.com$"

需要注意，正则匹配中的.是代表匹配一个任意字符，因此需要加上转义。还要注意，在许多语言中，你需要转义反斜杠本身，因此需要使用\.。