如何使用Phoenix在Ecto查询中按类别选择具有最大日期分组的ID? [英] How to select id with max date group by category in Ecto query with Phoenix?

问题描述

例如，我想按类别选择最大日期分组的ID，结果是:7、2、6

For an example, I would like to select id with max date group by category, the result is: 7, 2, 6

id  category  date
1   a         2013-01-01
2   b         2013-01-03
3   c         2013-01-02
4   a         2013-01-02
5   b         2013-01-02
6   c         2013-01-03
7   a         2013-01-03
8   b         2013-01-01
9   c         2013-01-01

这是我认为可以使用的SQL:

This is the SQL I think can work:

SELECT * FROM Table1 t1
JOIN 
(
   SELECT category, MAX(date) AS MAXDATE
   FROM Table1
   GROUP BY category
) t2
ON T1.category = t2.category
AND t1.date = t2.MAXDATE

但是如何将其转换为对Ecto的查询?

But how to translate that into a query on Ecto?

推荐答案

您可以使用

这篇关于如何使用Phoenix在Ecto查询中按类别选择具有最大日期分组的ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！