1、转换日期格式（转换日期格式）
SELECT DATE_FORMAT(day, '%W, %M %e, %Y') AS day FROM daysselect date('2021-4-3 15:57:28') --'2021-4-3'转化成时分秒 -- 查找最长的电话
with r as (
(select contact_id, type, duration from calls where type = 'incoming' order by duration desc limit 3)
union all
(select contact_id, type, duration from calls where type = 'outgoing' order by duration desc limit 3)
)
select first_name, type, date_FORMAT(from_unixtime(duration), '%H:%i:%s') AS duration_formatted from r, contacts where r.contact_id = contacts.id order by type desc, duration desc, first_name desc2、怎么获取2020年6月份这个条件，以下是可行方法（上月播放的儿童适宜电影）
（1）使用BETWEEN AND
program_date BETWEEN '2020-06-01' AND '2020-06-30'（2）使用 YEAR()和MONTH()函数
YEAR(program_date) = 2020 AND MONTH(program_date) = 06（3）使用 datediff() 或者 timestampdiff() 函数
DATEDIFF('2020-06-01', program_date) BETWEEN 0 AND 29timestampdiff(DAY,'2020-06-01', program_date)（4）使用 like 查询
program_date like "2020-06%"（5）使用dateadd（）函数
program_date between '2020-06-01' and date_add('2020-06-01', interval 29 DAY)3、取两个字段的最小值和最大值（按年度列出销售总额）
least(date('2018-12-31'), period_end)greatest(date('2018-01-01'), period_start))4、正则表达式的使用（查找拥有有效邮箱的用户）
select user_id, name, mail from Users where mail
regexp '^[A-Za-z][A-Za-z0-9_.\\-]*@leetcode[.]com$'select regexp_substr(tweet, '#.+?\\b') hashtag, count(1) hashtag_count
from tweets
group by 1
order by 2 desc, 1 desc
limit 35、not in 不包括null
SQL中的in、not in语句遇到null时的坑点_shenzhou_yh的博客-优快云博客
not in的效率不如not exists的效率高 -- 页面推荐Ⅱ、Leetcodify 好友推荐
6、（a, b）in (select a, b from t) -- 每位学生的最高成绩
SELECT student_id, MIN(course_id) AS course_id, grade
FROM Enrollments
WHERE (student_id, grade) IN (SELECT student_id, MAX(grade)
FROM Enrollments
GROUP BY student_id)
GROUP BY student_id ORDER BY student_id7、聚合函数不能嵌套，例如max(avg(a))，可以改成 order by avg(a) limit 1 或者 all(avg(a))
例如：电影评分
(select name as results from MovieRating
join Users using(user_id) group by user_id
order by count(movie_id) desc, name limit 1)
union all
(select title as results from MovieRating
join Movies using(movie_id) where created_at like '2020-02%'
group by movie_id order by avg(rating) desc, title limit 1)SELECT order_id FROM OrdersDetails GROUP BY order_id
HAVING max(quantity)
> ALL (SELECT AVG(quantity) FROM OrdersDetails GROUP BY order_id)位运算聚合 -- 按位用户权限分析
select
bit_and(permissions) as common_perms,
bit_or(permissions) as any_perms
from
user_permissions8、 group by 只保留每组的第一行 -- 锦标赛优胜者
select group_id, player_id
from (
select players.*, sum(if(player_id = first_player,
first_score, second_score)) score
from players join matches
on player_id = first_player or player_id = second_player
group by player_id
order by score desc, player_id
) tmp
group by group_id9、join 和 using的使用
select o.customer_id,c.name
from Orders o
left join Customers c using(customer_id)
left join Product p using(product_id)
where order_date like "2020-06%" or "2020-07%"
group by customer_id
having sum(case when order_date like "2020-06%"
then quantity*price else 0 end )>=100
and sum(case when order_date like "2020-07%"
then quantity*price else 0 end )>=100 10、sum的使用 -- 苹果和橘子的个数
select sum(a.apple_count + ifnull(b.apple_count,0)) as apple_count,
sum(a.orange_count + ifnull(b.orange_count,0)) as orange_count
from Boxes a left join Chests b using(chest_id);注意：如果存在一对多的情况，sum是对源数据分别求和，而不是对连接后的数据进行求和
11、case when 和 if的使用(计算布尔表达式的值)
select e.*,
case
when operator = '=' and v1.value = v2.value then 'true'
when operator = '<' and v1.value < v2.value then 'true'
when operator = '>' and v1.value > v2.value then 'true'
else 'false'
end as value
from Expressions e
join Variables v1
on e.left_operand = v1.name
join Variables v2
on e.right_operand = v2.name12、窗口函数 rank() over(partition by a order by b) (学生地理信息报告)
select
max(case when continent = 'America' then name else null end) America,
max(case when continent = 'Asia' then name else null end) Asia,
max(case when continent = 'Europe' then name else null end) Europe
from
(select
name,
continent,
row_number()over(partition by continent order by name) cur_rank
from
student)t
group by cur_rankrange between逻辑窗口的使用 -- 餐馆营业额变化增长
WITH subquery AS (
SELECT DISTINCT visited_on,
SUM(amount) OVER(ORDER BY visited_on
RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) AS amount
FROM Customer
)
SELECT visited_on, amount, ROUND(amount / 7, 2) AS average_amount
FROM subquery
WHERE visited_on - INTERVAL 6 DAY >=
(
SELECT MIN(visited_on) FROM Customer
);lag 和 lead 函数的使用 -- 访问日期之间最大的空档期
select user_id, max(datediff(next_date, visit_date)) as biggest_window
from(select user_id, visit_date, lead(visit_date, 1, '2021-1-1')
over(partition by user_id order by visit_date)
as next_date from UserVisits) t group by user_idmax和min函数的使用 -- 计算滞留雨水
with a as (
select
id, height,
least(max(height) over(order by id), max(height) over(order by id desc)) flag
from heights
)
select sum(if(flag - height > 0, flag - height, 0)) as total_trapped_water from a找连续区间 -- 连续空余座位 II
with t as(
select seat_id, (seat_id - rank() over(order by seat_id)) as diff
from cinema where free = 1 order by seat_id ),
t2 as(
select min(seat_id) as first_seat_id, max(seat_id) as last_seat_id,
(max(seat_id) - min(seat_id) + 1) as consecutive_seats_len from t
group by diff order by diff)
select first_seat_id, last_seat_id, consecutive_seats_len from t2
where consecutive_seats_len = (select max(consecutive_seats_len) from t2)
13、on 、where 和having的区别
SQL中常见问题and,where,having_一定要努力努力再努力的博客-优快云博客
14、递归语法（找到遗失的ID）
with recursive tmp as (
select 1 as n union all
select n + 1 from tmp where n < (select max(customer_id) from Customers ) )
select n as ids from tmp
where n not in ( select customer_id from Customers )进阶版--一行拆成多行，注意主键连接（查找热门话题标签 II）
with recursive tmp as (
select SUBSTRING_INDEX(SUBSTRING_INDEX(tweet,' ', 1), ' ',-1) as w, 1 as n, tweet_id as tid from tweets union all
select SUBSTRING_INDEX(SUBSTRING_INDEX(tweet,' ', n + 1), ' ',-1) as w, n + 1, tweet_id from tmp, tweets
where n < LENGTH(tweet) - LENGTH(REPLACE(tweet,' ','')) + 1 and tid = tweets.tweet_id )
select w as hashtag, count(1) as count from tmp where w regexp '#.+?\\b' group by w order by count desc, hashtag desc limit 3进阶版--树状分析 (首席执行官下属层级)
with recursive tmp as (
select 0 as n, employee_id, employee_name, manager_id, salary from Employees where manager_id is null
union all
select n + 1, Employees.employee_id, Employees.employee_name, Employees.manager_id,
Employees.salary from tmp, Employees
where tmp.employee_id = Employees.manager_id
)
select tmp.employee_id as subordinate_id, tmp.employee_name as subordinate_name,
n as hierarchy_level, (tmp.salary - Employees.salary) as salary_difference
from tmp, Employees where Employees.manager_id is null and tmp.manager_id is not null15、update-set的使用（变更性别）
update salary set
sex=replace(sex,'f','t'),
sex=replace(sex,'m','f'),
sex=replace(sex,'t','m');set后面的语句具有顺序性，select c1, c2 from t 中的 c1, c2也按顺序执行
16、巧用连接 -- 员工薪水中位数
select min(e1.id) id, e1.company, e1.salary
from employee e1, employee e2
where e2.company=e1.company
group by e1.company, e1.salary
having sum(case when e2.salary>=e1.salary then 1 else 0 end) >= count(*)/2
and sum(case when e2.salary<=e1.salary then 1 else 0 end)>=count(*)/2select name as customer_name,c.customer_id,o1.order_id,o1.order_date
from Orders o1,Orders o2,Customers c
where o1.customer_id=o2.customer_id and o1.order_date<=o2.order_date and o1.customer_id=c.customer_id
group by o1.order_id
having count(o2.order_date)<=3
order by customer_name,c.customer_id,order_date descselect c2.name as country
from Calls c1,Person p,Country c2
where (p.id=c1.caller_id or p.id=c1.callee_id) and c2.country_code=left(p.phone_number,3)
group by c2.country_code
having avg(duration) > (select avg(duration) from Calls)注：join连接后的结果最多不超过笛卡尔积的数量，应保证caller_id与callee_id不同，这是因为当两者相同时，与一条数据行连接后的数量仍为1个，所以对于大满贯数量，可以采取如下方法：
select p.player_id,p.player_name,
sum(player_id=wimbledon) + sum(player_id=fr_open)
+sum(player_id=us_open) + sum(player_id=au_open) as grand_slams_count
from players p join championships c
group by p.player_id, p.player_name
having grand_slams_count > 017、使用变量记录中间结果 -- 不同性别每日分数总计
select gender,day,
case when gender = 'F'
then @pre1 := @pre1+score_points
else
@pre2 := @pre2 +score_points
end total
from Scores,
(select @pre1 := 0,@pre2 := 0) a
order by gender asc,day asc多变量赋值采用 & 进行连接，由于赋值语句的优先级最低，所以需要加括号，例如：
if(@a > 10, (@a := 1) & (@b := @b + 1), @a := @a + 1)18、构建数据表 -- 统计实验的数量
with t1 as
(select 'Android' platform union select 'IOS' platform
union select 'Web' platform),
t2 as
(select 'Reading' experiment_name union select 'Sports' experiment_name
union select 'Programming' experiment_name)
select t1.platform, t2.experiment_name,
count(experiment_id) as num_experiments
from t1 cross join t2 left join Experiments
on t1.platform=Experiments.platform and
t2.experiment_name=Experiments.experiment_name
group by platform, experiment_name
order by platform, experiment_name19、group_concat()的使用 -- Build the Equation
select concat(group_concat(concat(if(factor >= 0, '+', ''),
factor, if(power > 1, concat('X^', power), if(power = 1, 'X', '') ))
order by power desc SEPARATOR ''), '=0') as equation from Terms20、子查询在没有结果时返回null -- 176. 第二高的薪水
SELECT
(SELECT DISTINCT
Salary
FROM
Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1) AS SecondHighestSalary;以上在mysql 8.0及以上版本测试通过
参考资料
SQL 控制窗口范围ROWS和RANG区别_naiyoumianbaohaohaoc的博客-优快云博客
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
