基于 GROUP BY 中的单个列使用 DISTINCT [英] Using DISTINCT based on a single column in GROUP BY

问题描述

这个问题经常出现，但我还没有找到对我有意义或适用的答案.

This question tends to come up a lot, but I haven't quite found an answer that makes sense to me or is applicable.

考虑这张表:

order_id   user_id   order_date
1           1        2012-01-01
1           1        2012-01-15
2           2        2012-01-15
2           2        2012-01-20
2           2        2012-01-25
3           2        2012-01-15
3           2        2012-01-16
4           2        2012-01-15
4           2        2012-01-16

我想根据 order_date 上的某些条件检索 user_id 不同的 (order_id, user_id).

I want to retrieve (order_id, user_id) where the user_id is distinct, based on some conditions on the order_date.

到目前为止我有这个查询:

So far I have this query:

SELECT user_id, order_id, MAX(order_date) FROM orders
GROUP BY order_id
HAVING
MAX(order_date) < '2012-01-20'

哪个返回

user_id   order_id   MAX(order_date)
1          1         2012-01-15
2          3         2012-01-16
2          4         2012-01-16

但是，我希望这个结果集也基于最大 order_id 对 user_id 进行分组.我要:

However, I want this result set to also group on on user_id, based on the maximum order_id. I want:

user_id   order_id   MAX(order_date)
1          1         2012-01-15
2          4         2012-01-16

如何根据 order_id 进行 GROUP BY，然后对 user_id 进行分组(或者可能是不同的，如果我放弃了拥有 MAX order_id 的要求)?

How can I do a GROUP BY based on order_id, and then subsequently group (or possibly distinct, if I drop the requirement of having the MAX order_id) on user_id?

推荐答案

怎么样?

SELECT S1.user_id, S1.order_id, S1.maxDate from
    (SELECT o1.user_id, o1.order_id, MAX(o1.order_date) maxDate FROM o1
    GROUP BY o1.order_id
    HAVING MAX(o1.order_date) < '2012-01-20') S1
LEFT JOIN o2 ON (S1.user_id = o2.user_id AND S1.order_id < o2.order_id)
WHERE (o2.order_id IS null)

注意:o1 和 o2 都是订单表

Note: o1 and o2 are both the orders table

这应该可以正常工作，否则我可能误解了要求.这是一个示例

This should work as expected or I might have misunderstood the requirements. Here is an example

这篇关于基于 GROUP BY 中的单个列使用 DISTINCT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！