MySQL - 按 DESC 和 GROUP BY 排序

Question

我有一个customers表，其中包含其字段，例如userId，customerId和created。

这是表中customers数据

userId    customerId    created
user_1    customer_1    2016-03-04 00:00:00
user_1    customer_2    2016-10-04 00:00:00
user_1    customer_3    2016-07-04 00:00:00
user_1    customer_4    2016-09-04 00:00:00
user_2    customer_5    2016-06-04 00:00:00
user_2    customer_6    2016-03-04 00:00:00

我使用以下一些查询来获取每个用户的最新版本。这是我尝试过的查询之一

select *
from customers
order by created desc
group by userId

上述查询无法正常工作。我想要的结果是：

user_1    customer_2    2016-10-04 00:00:00
user_2    customer_5    2016-06-04 00:00:00

也许我不清楚order by和group by命令是如何工作的。你能给我一些解决方案吗？

Answer 1

SELECT c1.userId, c1.customerId, c1.created
FROM customers c1
INNER JOIN
(
    SELECT userId, MAX(created) AS maxCreated
    FROM customers
    GROUP BY userId
) c2
    ON c1.userId = c2.userId AND c1.created = c2.maxCreated
ORDER BY c1.created

请注意，您不需要在 ORDER BY 子句中显式使用 DESC，因为默认情况下 MySQL 按降序排序。

Answer 2

试试这个：

SELECT 
    *
FROM 
    customers c1
WHERE 
    (userId, created) IN 
        (
            SELECT userId, MAX(created)
            FROM customers c2
            WHERE c1.userId = c2.userId
        );

Answer 3

如果你尝试以这种方式做，你会得到你想要的结果：

SELECT * FROM customers WHERE created = (select max(created) from customers a where a.userId = customers.userId )

分组依据子句允许您获得最大值：

select userId,max(created) from customers group by userId

以便您获得每个 userId 的最大日期，并将其放入一些值后，您可以在另一个查询中使用。

类似这样的东西只给你一行最大日期：

select userId,customerId,max(created) from customers order by created desc