将有序表分组总是返回第一行吗? MySQL数据库 [英] Will grouping an ordered table always return the first row? MYSQL

问题描述

我正在编写一个查询，在其中将行的选择分组以找到其中一列的MIN值.

I'm writing a query where I group a selection of rows to find the MIN value for one of the columns.

我还想返回与返回的MIN行关联的其他列值.

I'd also like to return the other column values associated with the MIN row returned.

例如

ID QTY PRODUCT TYPE
--------------------
1  2   Orange  Fruit
2  4   Banana  Fruit
3  3   Apple   Fruit

如果我通过"TYPE"列对表进行分组并选择MIN数量，它将不会返回MIN行的对应产品，在上述情况下为"Apple".

If I GROUP this table by the column 'TYPE' and select the MIN qty, it won't return the corresponding product for the MIN row which in the case above is 'Apple'.

在分组之前添加ORDER BY子句似乎可以解决此问题.但是，在继续将此查询包含在我的应用程序中之前，我只想知道此方法是否将始终返回正确的值.这是正确的方法吗?我已经看到了一些使用子查询的示例，但是我也读到这种效率低下.

Adding an ORDER BY clause before grouping seems to solve the problem. However, before I go ahead and include this query in my application I'd just like to know whether this method will always return the correct value. Is this the correct approach? I've seen some examples where subqueries are used, however I have also read that this inefficient.

谢谢.

推荐答案

在分组之前添加ORDER BY子句似乎可以解决此问题.但是，在继续将此查询包含在我的应用程序中之前，我只想知道此方法是否将始终返回正确的值.这是正确的方法吗?我已经看到了一些使用子查询的示例，但是我也读到这种效率低下.

Adding an ORDER BY clause before grouping seems to solve the problem. However, before I go ahead and include this query in my application I'd just like to know whether this method will always return the correct value. Is this the correct approach? I've seen some examples where subqueries are used, however I have also read that this inefficient.

不，这不是正确的方法.

No, this is not the correct approach.

我相信您正在谈论这样的查询:

I believe you are talking about a query like this:

SELECT  product.*, MIN(qty)
FROM    product
GROUP BY
        type
ORDER BY
        qty

您在这里使用的是MySQL扩展名，它使您可以选择GROUP BY查询中未聚合/未分组的列.

What you are doing here is using MySQL's extension that allows you to select unaggregated/ungrouped columns in a GROUP BY query.

这主要用于在PRIMARY KEY上同时包含JOIN和GROUP BY的查询，例如:

This is mostly used in the queries containing both a JOIN and a GROUP BY on a PRIMARY KEY, like this:

SELECT  order.id, order.customer, SUM(price)
FROM    order
JOIN    orderline
ON      orderline.order_id = order.id
GROUP BY
        order.id

在这里，order.customer既没有分组也没有聚合，但是由于您是在order.id上分组，因此可以保证每个组中的值都相同.

Here, order.customer is neither grouped nor aggregated, but since you are grouping on order.id, it is guaranteed to have the same value within each group.

对于您来说，qty的所有值在组中都有不同的值.

In your case, all values of qty have different values within the group.

不能保证引擎将从组中的哪个记录中获取值.

您应该这样做:

SELECT  p.*
FROM    (
        SELECT  DISTINCT type
        FROM    product p
        ) pd
JOIN    p
ON      p.id = 
        (
        SELECT  pi.id
        FROM    product pi
        WHERE   pi.type = pd.type
        ORDER BY
                type, qty, id
        LIMIT 1
        )

如果您在product (type, qty, id)上创建索引，此查询将快速运行.

If you create an index on product (type, qty, id), this query will work fast.

这篇关于将有序表分组总是返回第一行吗? MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！