如何通过在启用 ONLY_FULL_GROUP_BY 的情况下在 GROUP BY 中指定单个列名来使分组工作 [英] How to make group by work by specifying a single column name in GROUP BY with ONLY_FULL_GROUP_BY enabled

问题描述

基本上我只是在寻找与此 sql 语句等效的语句:

Basically I am just looking for an equivalent of this sql statement:

SELECT * FROM products GROUP BY merchant_id ORDER BY id DESC LIMIT 10;

这在 ONLY_FULL_GROUP_BY 被禁用时工作正常，但当它被启用时，它不起作用.上面的等效 sql 语句是什么使其在 ONLY_FULL_GROUP_BY 模式下工作?

This works fine when ONLY_FULL_GROUP_BY is disabled, but when its enabled, it doesn't work. What is the equivalent sql statement for the above to make it work on the ONLY_FULL_GROUP_BY mode?

我想要做的是假设我有 100 种 ID 为 1 到 100 的产品，它们分布在 10 个商家之间.所以我想列出最新的 10 种产品，每个商家 1 种.

What I am trying to do is lets say I have 100 products with ids 1 to 100 that are distributed among 10 merchants. So I want to list out the latest 10 products, 1 from each merchant.

编辑这是一个示例表和预期的输出.(假设商家 1、商家 2 等是 merchnat_id 的辅助键列)所以如您所见，我需要的是来自每个唯一商家的一种产品(具有最高主键的产品，即该商家的最新产品).

EDIT Here is a sample table and expected output. (assume merchant 1, merchant 2 etc is the secondary key column for merchnat_id) So as you can see, what i need is one product(the one with the highest primary key ie the latest for that merchant) from each unique merchant.

推荐答案

在子查询中获取每个商家的 max(id) 并在外部查询中将其连接回产品表以获取其他字段:

In a subquery grab the max(id) per merchant and in the outer query join this back to the products table to get the other fields:

select p.*
from products p
inner join (select merchant_id, max(id) as max_id
            from products
            group by merchant_id
            order by max(id) desc
            limit 10) t1 on p.id=t1.max_id

你不应该真正依赖 MySQL 如何实现宽松的 group by 子句，因为这可能会在没有任何通知的情况下改变.请记住:即使关闭 ONLY_FULL_GROUP_BY sql 模式，也不能保证问题中的查询会产生预期的输出！

You should not really rely on how MySQL implements the relaxed group by clause because that may change without any notice. Remember: nothing guarantees even if ONLY_FULL_GROUP_BY sql mode is turned off, that the query in the question would produce the expected output!

这篇关于如何通过在启用 ONLY_FULL_GROUP_BY 的情况下在 GROUP BY 中指定单个列名来使分组工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！