【SQL基础】【leetcode】SQL50题

news/2024/9/14 2:06:28/ 标签: sql, leetcode, linux

查询

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

题目链接

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

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

（2）寻找用户推荐人

题目链接

sql">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）大的国家

题目链接

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

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

（4）文章浏览1

题目描述

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

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

（5）无效的推文

题目描述

sql">SELECT tweet_id
FROM tweets
WHERE LENGTH(content) > 15

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

连接

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

题目描述

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

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

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

（2）产品销售分析 I

题目描述

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

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

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

题目描述

有两种做法。

sql"># Write your MySQL query statement belowSELECT customer_id, COUNT(v.visit_id) as count_no_trans
FROM Visits v
WHERE v.visit_id NOT IN (SELECT visit_idFROM Transactions)
GROUP BY customer_id
ORDER BY count_no_trans

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

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

sql">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)上升的温度

题目描述

sql">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)每台机器的进程平均运行时间

题目链接

sql">SELECT s.machine_id, ROUND(AVG(e.timestamp-s.timestamp), 3) as processing_time
FROM (SELECT machine_id, process_id, timestampFROM ActivityWHERE activity_type='start') as s,(SELECT machine_id, process_id, timestampFROM ActivityWHERE 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）员工奖金

题目描述

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

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

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

题目链接

sql">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名直接下属的经理

题目链接

sql">SELECT name
FROM (SELECT COUNT(managerId) as num,managerIdFROM EmployeeGROUP BY managerIdHAVING num>=5) as t, Employee e
WHERE e.id=t.managerId

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

（9）确认率

题目描述

sql">SELECT t1.user_id, IFNULL(ROUND((t2.num/t1.num), 2), 0) as confirmation_rate
FROM(SELECT user_id, COALESCE(t.num, 0) as numFROM(SELECT COUNT(user_id) as num,user_idFROM ConfirmationsGROUP BY user_id) as tRIGHT JOIN Signups USING(user_id)) as t1,(SELECT user_id, COALESCE(t.num, 0) as numFROM(SELECT COUNT(user_id) as num,user_idFROM ConfirmationsRIGHT JOIN Signups USING(user_id)WHERE action='confirmed'GROUP BY user_id) as tRIGHT JOIN Signups USING(user_id)) as t2
WHERE t1.user_id=t2.user_id
GROUP BY t2.user_id