sql语句使用技巧

1、转换日期格式（转换日期格式）

SELECT DATE_FORMAT(day, '%W, %M %e, %Y') AS day FROM days

select date('2021-4-3 15:57:28') --'2021-4-3'

2、怎么获取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 29

timestampdiff(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 3

5、not in 不包括null

SQL中的in、not in语句遇到null时的坑点_shenzhou_yh的博客-CSDN博客

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_id

7、聚合函数不能嵌套，例如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)

8、 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_id

9、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.name

12、窗口函数 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_rank

range 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_id

max和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

13、on 、where 和having的区别

SQL中常见问题and,where,having_一定要努力努力再努力的博客-CSDN博客

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

 15、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(*)/2

最近的三笔订单

select 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 desc

 可以放心投资的国家

select 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 > 0

 17、使用变量记录中间结果 -- 不同性别每日分数总计

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_name

19、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 Terms

20、子查询在没有结果时返回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的博客-CSDN博客

SQL文中取两个字段的最小值_weixin_33713350的博客-CSDN博客

MySQL group_concat()详解_奋斗的龙猫的博客-CSDN博客_group_concat()