Mysql 获取订单表中每个会员的最新订单

SELECT 
    uid,
    MAX(id) id 
  FROM
    project_order 
  GROUP BY uid

说明：GROUP BY 分组 col 中必须有分组的字段出现，其他字段只能使用聚合函数，否则会报错

SELECT 
    uid,
    MAX(expire_time) expire_time,MAX(id) id ,order_no
  FROM
    project_order 
  GROUP BY uid

报错：错误代码： 1055
Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'movie.project_order.order_no' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

可以看出是sql_mode 问题。这里不再陈述

方法一：

SELECT 
   a.*
FROM
  project_order a 
WHERE 1 > 
  (SELECT 
    COUNT(*) 
  FROM
    project_order 
  WHERE uid = a.uid 
    AND id > a.id) 
ORDER BY a.uid 

说明：这种方式可以输出任意你想要的字段

还有以下几种：

方法二：

 SELECT 
   a.*
FROM
  project_order a 
  INNER JOIN 
    (SELECT 
      uid,
      MAX(id) id 
    FROM
      project_order 
    GROUP BY uid) b 
    ON a.uid = b.uid 
    AND a.id = b.id 
ORDER BY a.uid

 SELECT 
  a.*
FROM
  project_order a 
WHERE NOT EXISTS 
  (SELECT 
    1 
  FROM
    project_order 
  WHERE uid = a.uid 
    AND id > a.id)

SELECT 
  a.*
FROM
  project_order a 
WHERE id = 
  (SELECT 
    MAX(id) 
  FROM
    project_order 
  WHERE uid = a.uid) 
ORDER BY a.uid 

SELECT 
  a.*
FROM
  project_order a,
  (SELECT 
    uid,
    MAX(id) id 
  FROM
    project_order 
  GROUP BY uid) b 
WHERE a.uid = b.uid 
  AND a.id = b.id 
ORDER BY a.uid 

说明：当uid没有设置为索引的时候，以上执行效率差别明细，当设置uid为索引时，差别几乎可以忽略。

 

转载于:https://www.cnblogs.com/quanzhongkeji/p/9621448.html