mysql 分组后排序

1、主要借助 SUBSTRING_INDEX 、 GROUP_ConCAT 函数

SELECt
  product_id,
  branch,
  SUBSTRING_INDEX(GROUP_CONCAt(t.stock ORDER BY t.stock DESC ),',',1) stock
FROM (SELECt *
      FROM product_stock) t
GROUP BY product_id,branch

2、通过关联查询及COUNT函数实现

SELECt *
FROM (SELECt
        t.product_id,
        t.branch,
        t.stock,
        COUNT(*)     AS rank
      FROM product_stock t
        LEFT JOIN product_stock r
          ON t.product_id = r.product_id
            AND t.branch = r.branch
            AND t.stock <= r.stock
      GROUP BY t.id) s
WHERe s.rank = 1

3、OVER(PARTITION BY… ORDER BY…) 注意MySQL的版本，版本低不能使用

4、在SQL里面使用局部变量

select * from（
	select case when @pid = b.a_time then @row:=@row+1 else @row:=1 end rowId,id,a_time
	from t_table b,(select @pid:=null)r order by a_time desc)a
	where a.rowId = 1 and date_format(a_time,'%Y-%m-%d') <= date_format(?,'%Y-%m-%d')
）

参考文档：https://blog.csdn.net/m0_37797991/article/details/80511855