平安夜，秀一波一道SQL题的7种解法

今晚是平安夜，明天是圣诞节啦，周围一片欢愉的过节氛围，祝小破号的朋友们节日快乐......

祝福送到了，苹果就不送了，送你们一道SQL题吧！（别打我）

题目是群里小伙伴贡献的，巧是巧的，我之前秋招的时候也遇到了类似的题目，只是换了场景嵌套，这种题目换汤不换药，换件衣服还能提出N个问题，一起来看看吧！

有这么一张表，表中有两列数据，一列是用户id，一列是订单金额，一个用户可能存在多个订单，也就对应了多个订单金额。

找出所有订单金额都大于20元的用户id。

先来提下不同的解题思路：

1.找出订单小于20的id 再left join

2.聚合得到每个用户的订单数，和大于20的订单数，比一比

3.用窗口函数找到min 再用where 筛选

4.均大于计数=用户出现次数

5.找出每个用户最小金额且最小金额大于20的

说完大概的解题思路，直接上7种解法，所谓的7种解法其实逃不脱上述解题思路，只是函数用的不一样，我把我能想到的几种可能写法都写了。

每个人可能都有自己首选的写法，这7种都写出来是为了辅助一些同学改变自己的思维，在下次遇到同样的问题时，在面对考官问询“有没有其他解法？”，“你觉得最优解法是什么？”时，有多个选择。

解法1：（用时0.001641s）

select id 
from test_ordr
group by id
having min(amt) > 20;

解法2：（用时0.00127s）

select id 
from 
(
    select id,min(amt) as min_amt 
    from test_ordr 
    group by id 
)as t 
where min_amt > 20;

解法3：（用时0.001s）

select id 
from 
(
  select id 
        ,min(amt) over (partition by id) as min_amt 
  from test_ordr
)as t 
where min_amt > 20 
group by id;

解法1、2、3其实都是从找到每个用户最小订单金额出发，如果此用户的最小金额都大于20元，那么此用户所以订单金额势必都大于20元。只是有的用了用于组记录筛选的having子句，有的用了from子查询，有的用了窗口函数，殊途同归吧。

不过需要注意，对于这种题目，如果只要求筛选出一个字段，不要求保留全体数据的话，窗口函数好像就没什么必要了，免得被面试官说感觉有点复杂，让你说别的方法，或者在你提及窗口函数后一看，呦，懂窗口函数？来，再出道题好好考考你！

解法4：（用时0.000525s）

select id 
from test_ordr 
where id not in (select id from test_ordr where amt <= 20)
group by id;

解法4的思路是找出有订单金额不大于20元的用户，将这些用户剔除，使用了关键字not in，in、not in都是常用的关键字，但向来口碑不太好，大多数人都建议尽量避免in和not in的使用，因为效率低且易出错，可以使用exists、not exists代替，也可以用join代替，此处只是举例说明，但是不建议用。

解法5：（用时0.001018s）

select id 
from  test_ordr 
group by id 
having sum(1) = sum(case when amt > 20 then 1 else 0 end);

解法6：（用时0.001614s）

select id 
from 
(
  select id 
        ,sum(1) as cnt 
        ,sum(case when amt > 20 then 1 else 0 end) as cnt2
  from  test_ordr 
  group by id 
)as t 
where cnt = cnt2;

解法7：（用时0.002292s）

select t1.id
from 
(
  select id 
  from test_ordr 
  group by id 
) as t1
left join 
(
  select id 
  from test_ordr 
  where amt <= 20
  group by id 
) as t2 
on t1.id = t2.id 
where t2.id is null

解法5、6、7其实也是一个思路，比较用户所有的订单数与用户所有大于20元的订单数，如果相同则证明所有订单都是大于20元的。只是有的用的是having子句，有的用from子查询，有的用表关联，此时一些执行时间、数据量、效率问题就体现出来了。我本人是非常不建议表关联的，能不关联就不关联。除此之外，在进行条件判断时，千万不要忘了好用的case when。

7种解法介绍完了，上述7种都是我随手写的，如果有误，欢迎指正。除此外应该还有一些别的写法或关键字或子句或函数的使用能解决上述问题，思路最重要。

每种解法都附带了一个用时，此用时是我在mysql 10.0运行得到的，表中共有12条数据，本来我是想借用时来讲一下效率的问题，但好笑的是，在这些数据条数情况下，用时最少的竟然是解法4，也就是所谓效率低的not in，但这并不能说明它效率高，应该只是数据量的问题。这里有见解的朋友，欢迎后台私信我讨论。

耗时最久的，不负众望，left join！所以啊，朋友们，尽量减少满脑子都是left join的想法吧，多学点不join就能解决问题的方法。

基本都讲完了，留一道题目吧。

有张表，里面有三列，学生学号std_id，课程号course_id，课程成绩score，求所有课程分数都大于80分的学生id。

对了，诚心邀请公众号的小伙伴进群交流探讨，后台回复关键字「开心交流群」进群交流。

我是胖里，希望今天的分享于你有益，感谢关注，下次再聊。