SQL50题

草仔细一想其实完全没怎么写过SQL语句

查询

1757. 可回收且低脂的产品

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

注意代码规范，比如关键字大写，合适的换行等

584. 寻找用户推荐人

SELECT c.name
FROM Customer AS c
WHERE c.referee_id != 2 
OR c.referee_id 
IS null
;

考虑到id可能为null的情况

595. 大的国家

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

1148. 文章浏览 I

SELECT DISTINCT author_id AS id
FROM Views
WHERE viewer_id = author_id
ORDER BY author_id ASC;

DISTINCT关键字可以起到去重的效果，会在根据条件查询结束后，删除重复的结果

GROUP BY同样可以实现去重，但其逻辑是根据查询条件过滤再查询

distinct 特点：

如：select distinct name, sex from tb_students 这个sql的语法中，查询 tb_students 表中 name, sex 并去除名字和性别都重复的学生：

1、distinct 只能放在查询字段的最前面，不能放在查询字段的中间或者后面。

备注：select sex, distinct name from tb_students 这种写法是错误的，distinct 只能写在所有查询字段的前面

2、distinct 对后面所有的字段均起作用，即 去重是查询的所有字段完全重复的数据，而不是只对 distinct 后面连接的单个字段重复的数据。

备注：也就是 distinct 关键字对 name, sex 都起作用，去重姓名、性别完全一样的学生，如果姓名相同、性别不同是不会去重的。

3、要查询多个字段，但只针对一个字段去重，使用 distinct 去重的话是无法实现的。

group by 特点：

1、一般与聚类函数使用（如count()/sum()等），也可单独使用。

2、group by 也对后面所有的字段均起作用，即 去重是查询的所有字段完全重复的数据，而不是只对 group by 后面连接的单个字段重复的数据。

3、查询的字段与 group by 后面分组的字段没有限制。

group by前后筛选分别使用where和having

效率

distinct会扫描全表，而group by相当于建立了索引，在大量数据情况线下后者性能更优

1683. 无效的推文

SELECT tweet_id
FROM Tweets
WHERE char_length(content) > 15
;

处理非 ASCII 字符（比如汉字）时的行为： char_length('刘') = 1(字符)， length('刘') = 3(字节)

连接

有一张经典图片

很好的解释了join系列命令的结果，事实上就如同字面意义，join本质上是将两张表连接起来输出，只不过有多种连接方式

1378. 使用唯一标识码替换员工ID

SELECT eu.unique_id, e.name
FROM Employees e
LEFT JOIN EmployeeUNI eu
ON e.id = eu.id;

left join，它以左表为基准，返回左表中所有的行，同时返回右表中与左表匹配的行。如果右表中没有匹配的行，则用NULL填充

1068. 产品销售分析 I

SELECT p.product_name, s.year, s.price
FROM Sales s
LEFT JOIN Product p 
ON p.product_id = s.product_id
;

题目要求

获取 Sales 表中所有 sale_id 对应的

所以将sales表作为左表进行左连接

1581. 进店却未进行过交易的顾客

SELECT v.customer_id, count(customer_id) count_no_trans
FROM Visits v 
LEFT JOIN Transactions t 
on v.visit_id = t.visit_id
WHERE transaction_id is null
GROUP BY customer_id
;

使用count、sum等函数时常常要想到需要先分组（即使用group by）如果不分组的话，对于count函数，输出只有一行，因此只会输出一行结果，从逻辑和期待结果角度都是错误的

197. 上升的温度

SELECT b.Id
FROM Weather as a,Weather as b
WHERE a.Temperature < b.Temperature and DATEDIFF(a.RecordDate,b.RecordDate) = -1;

自连接

1661. 每台机器的进程平均运行时间

SELECT a1.machine_id AS 'machine_id', 
    ROUND(AVG(a2.timestamp - a1.timestamp),3)  AS 'processing_time'
FROM Activity AS a1
INNER JOIN Activity AS a2
ON a1.machine_id = a2.machine_id
AND a1.process_id = a2.process_id
AND a1.activity_type = 'start' AND a2.activity_type = 'end'
GROUP BY a1.machine_id
;

round(num,dec) 将num四舍五入保留dec位

聚合函数

620. 有趣的电影

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

筛选奇数可以使用的方法：

1. id&1=1
2. id%2=1
3. mod(id,2)=1

1251. 平均售价

SELECT  p.product_id, ROUND(ifnull(SUM(units * price) / SUM(units), 0),2) AS average_price
FROM prices as p
LEFT JOIN unitsSold as u ON p.product_id = u.product_id
WHERE u.purchase_date BETWEEN p.start_date AND p.end_date or u.product_id is null
GROUP BY p.product_id

1075. 项目员工 I

select p.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 p.project_id

排序和分组

高级查询和连接

1731. 每位经理的下属员工数量

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

注意使用自连接时，最后使用的数据分别来自哪张表

子查询

子查询是将大问题分治解决的一种思想，也就是先查询得到部分结果，再用这部分结果查询

解决类似的问题可以使用多条sql语句实现，也可以用自连接实现

1978. 上级经理已离职的公司员工

select employee_id
from Employees
where salary < 30000
and manager_id not in (
    select employee_id
    from Employees
)
order by employee_id

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