MySQL中没有子查询字段的模式计算?
在我的应用程序中，每个产品组都有很多产品，每个产品都有一个制造商.这些关系由 MySQL 存储在带有 id 字段的 InnoDB 表 product_groups 和带有 id 字段的 products 中，product_group 和 manufacturer 字段.
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
现在我们有了子选择结果，我们需要为每个产品组挑选出 occurrences 字段中具有最大值的行.
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 字段对子选择进行分组，但这一次，仅 product_group 字段.现在，当我们在这里执行 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 确保制造商拥有最多外观出现在串联列表中首先.最后，我们用 ::: 分隔每个制造商.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
相关文章