MySQL 对最后一个进行排序和分组

Question

您好，我的 MYSQL 查询需要帮助

我有 table

id | tn   | title    | customer_id |create_time | comment              |
1  | 1342 | sample1  | customer1   | 2012-01-01 | hello world          |
2  | 1342 | sample1  | customer1   | 2012-01-02 | hello world          |
3  | 1342 | sample1  | customer1   | 2012-01-03 | hello new world      |
4  | 3362 | sample2  | customer1   | 2012-01-02 | good bye world       |
5  | 3362 | sample2  | customer1   | 2012-01-03 | good bye world       |
6  | 3362 | sample2  | customer1   | 2012-01-04 | good bye world       |
7  | 3362 | sample2  | customer1   | 2012-01-05 | good bye new world   |

当我按tn分组时，我采取了

1  | 1342 | sample1  | customer1   | 2012-01-01 | hello world          |
4  | 3362 | sample2  | customer1   | 2012-01-02 | good bye world       |

但我需要带走

3  | 1342 | sample1  | customer1   | 2012-01-03 | hello new world      |
7  | 3362 | sample2  | customer1   | 2012-01-05 | good bye new world   |

这就像按具有最大 id 或最大 create_time 的 tn 分组

我该怎么做？谢谢!

Answer 1

试试这个:

mysql> select * from ( select * from tbl2 tn order by id desc ) t group by tn;
+------+------+---------+-------------+-------------+--------------------+
| id   | tn   | title   | customer_id | create_time | comment            |
+------+------+---------+-------------+-------------+--------------------+
|    3 | 1342 | sample1 | customer1   | 2012-01-03  | hello new world    |
|    7 | 3362 | sample2 | customer1   | 2012-01-05  | good bye new world |
+------+------+---------+-------------+-------------+--------------------+
2 rows in set (0.02 sec)