在mysql之前按组排序数据（排序） [英] Sort data (order by) before group by in mysql

问题描述

我想从 sub_prd_id 中的数据进行分组。但在此之前，我需要从 max created_at 。

I want to group below data from sub_prd_id. but before that i need order data from max created_at.

的订单数据。

select * FROM prd_data group by sub_prd_id order by created_at desc

但是这个查询没有返回我所需要的。例如，根据以下数据，执行查询后，结果应如下所示：

But this query not returned what i need. for example, according to below data, after query executes, result should be as below,

id  name    sub_prd_id  created_at
4   Grape     10            2013-04-28 03:11:55
6   Banana    11            2013-04-28 03:23:14
7   Pineapple 12            2013-04-28 03:23:44

以下是包含数据的表结构

Here is Table Structure with data.

id  name    sub_prd_id  created_at
2   Apple     10            2013-04-28 03:04:51
3   Orange    10            2013-04-28 03:08:19
4   Grape     10            2013-04-28 03:11:55
5   Mango     11            2013-04-28 03:22:48
6   Banana    11            2013-04-28 03:23:14
7   Pineapple 12            2013-04-28 03:23:44

推荐答案

您要完成的操作称为分组最大值，可以'使用 ORDER BY 来实现。相反，必须找到 MAX（），然后将结果返回到表中：

What you're trying to accomplish is known as a groupwise maximum, which can't be achieved using ORDER BY. Instead, one must find the MAX() and then join the result back to the table:

SELECT prd_data.* FROM prd_data NATURAL JOIN (
  SELECT   sub_prd_id, MAX(created_at) created_at
  FROM     prd_data
  GROUP BY sub_prd_id
) t

在 sqlfiddle 。

这篇关于在mysql之前按组排序数据（排序）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！