在MySQL中没有子查询字段的模式计算? [英] Mode calculation without a subquery field in MySQL?

问题描述

在我的应用程序中，每个产品组都有很多产品，并且每个产品都有一个制造商.这些关系由MySQL存储在带有id字段的InnoDB表product_groups和带有id，product_group和manufacturer字段的products中.

In my application, each product group has many products, and each product has one manufacturer. These relations are stored by MySQL in InnoDB tables product_groups with an id field, and products with id, product_group and manufacturer fields.

是否有一种方法可以找到每个产品组中最常见的制造商，而不必选择子查询?

Is there a way to find the most common manufacturer in each product group, without resorting to selecting subqueries?

这是我目前的做法:

SELECT product_groups.id,
  (
    SELECT manufacturer FROM products
    WHERE product_group = product_groups.id
    GROUP BY manufacturer
    ORDER BY count(*) DESC
    LIMIT 1
  ) manufacturer_mode
FROM product_groups;

推荐答案

尝试以下解决方案:

SELECT
    a.product_group,
    SUBSTRING_INDEX(GROUP_CONCAT(a.manufacturer ORDER BY a.occurrences DESC SEPARATOR ':::'), ':::', 1) AS manufacturer_mode
FROM
    (
        SELECT
            aa.product_group,
            aa.manufacturer,
            COUNT(*) AS occurrences
        FROM
            products aa
        GROUP BY
            aa.product_group,
            aa.manufacturer
    ) a
GROUP BY
    a.product_group

说明:

这仍然使用一种子查询形式，但是这种子查询只执行一次，而不是像在您的原始示例中那样逐行执行的子查询.

Explanation:

This still uses a form of subquery, but one which executes only once as opposed to one that executes on a row-by-row basis such as in your original example.

首先选择product_group id，制造商以及每个特定组的制造商出现次数的计数即可.

It works by first selecting the product_group id, the manufacturer, and the count of how many times the manufacturer appears for each particular group.

执行后，FROM子选择将类似于以下内容(仅在此处构成数据):

The FROM sub-select will look something like this after execution (just making up data here):

product_group   |   manufacturer   |    occurrences
---------------------------------------------------
1               |   XYZ            |    4
1               |   Test           |    2
1               |   Singleton      |    1
2               |   Eloran         |    2
2               |   XYZ            |    1

现在有了子选择结果，我们需要为每个产品组选择occurences字段中具有最大值的行.

Now that we have the sub-select result, we need to pick out the row that has the maximum in the occurences field for each product group.

在外部查询中，我们再次通过product_group字段将子选择分组，但这次是 only 字段.现在，当我们在此处执行GROUP BY时，我们可以在MySQL中使用一个真正引人注目的函数，称为GROUP_CONCAT，我们可以使用该函数将制造商以所需的任何顺序连接在一起.

In the outer query, we group the subselect once again by the product_group field, but this time, only the product_group field. Now when we do our GROUP BY here, we can use a really compelling function in MySQL called GROUP_CONCAT which we can use to concatenate the manufacturers together and in any order we want.

...GROUP_CONCAT(a.manufacturer ORDER BY a.occurrences DESC SEPARATOR ':::'...

我们在这里所做的是将按product_group id分组在一起的制造商连接在一起，ORDER BY a.occurrences DESC确保出现最多的制造商 first 串联列表中的"strong>".最后，我们用:::分隔每个制造商. product_group 1的结果如下:

What we are doing here is concatenating the manufacturers together that are grouped together per product_group id, the ORDER BY a.occurrences DESC makes sure that the manufacturer with the most appearances appears first in the concatenated list. Finally we are separating each manufacturer with :::. The result of this for product_group 1 will look like:

XYZ:::Test:::Singleton

XYZ首先出现，因为它在occurance字段中具有最大值.我们仅要选择XYZ，因此我们将串联包含在SUBSTRING_INDEX中，这将允许我们仅基于:::分隔符选择列表的第一个元素.

XYZ appears first since it has the highest value in the occurance field. We only want to select XYZ, so we encase the concatenation within SUBSTRING_INDEX, which will allow us to only pick the first element of the list based on the ::: delimiter.

最终结果将是:

product_group    |    manufacturer_mode
---------------------------------------
1                |    XYZ
2                |    Eloran

这篇关于在MySQL中没有子查询字段的模式计算?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！