题解 | #找出每个学校GPA最低的同学#

select
    b.device_id,
    b.university,
    b.gpa
from
    (
        select
            a.device_id,
            a.university,
            a.gpa,
            rank() over (
                partition by
                    a.university
                order by
                    a.gpa asc
            ) as rk
        from
            user_profile a
    ) b
where
    b.rk = 1

方案一：开窗函数的最基础使用，我记得不用开窗也可以实现，待会儿用另一种方法来写

方案二：下面是用变量的方式做的，oracle和mysql的时候都各自写一份各自特点的解题方式，有些题用起来效率不错

SELECT 
  b.device_id,
  b.university,
  b.gpa 
FROM
  (SELECT 
    a.device_id,
    a.university,
    a.gpa,
    CASE
      WHEN a.university = @preCol 
      THEN 
      CASE
        WHEN a.gpa = @preGPA 
        THEN @rk 
        ELSE @rk := @rk + 1 
      END 
      ELSE @rk := 1 
    END AS rk,
    @preCol := a.university,
    @preGPA := a.gpa 
  FROM
    user_profile a,
    (SELECT 
      @preCol := NULL,
      @preGPA := 0,
      @rk := 0) b 
  ORDER BY a.university,
    a.gpa) b 
WHERE b.rk = 1