MySQL Group By 查找唯一组合

Question

首先为模糊的标题道歉，我真的不知道如何表达它，我想这就是我一直无法找到解决方案的原因。

使用修改后的 OSCommerce 安装，我试图在自定义报告的表格中找到订购产品选项/属性的独特组合。产品的每个选项/属性都存储在一行中。一种产品可以(并且确实)具有多个选项/属性。

我有以下 SQL 查询

SELECT
sum(op.products_quantity) AS attrCnt,
opa.products_options AS prodOption,
opa.products_options_values AS prodOptionValue
FROM
store_orders_products_attributes opa
LEFT JOIN store_orders o ON o.orders_id = opa.orders_id
LEFT JOIN store_orders_products op ON op.orders_products_id = opa.orders_products_id
WHERE
o.customers_id = '99999'
AND (o.date_purchased BETWEEN CAST('2017-01-03' AS DATE)
AND CAST('2017-02-01' AS DATE))
AND op.products_id = 88888
GROUP BY
opa.products_options_values
ORDER BY
opa.products_options,
opa.products_options_values

哪个返回:

+---------+---------------+-----------------+
| attrCnt |  prodOption   | prodOptionValue |
+---------+---------------+-----------------+
|       6 | Select Colour | Blue            |
|       1 | Select Colour | Yellow          |
|       1 | Select Size   | L               |
|       2 | Select Size   | M               |
|       4 | Select Size   | S               |
+---------+---------------+-----------------+

该时期订购的产品总数为 7 件，1 件大件，2 件中件，4 件小件，- 6 件蓝色，1 件黄色。由此我无法判断 L(大)是蓝色还是黄色等。

我想要的结果如下所示(无论是 L - Blue 还是 Blue - L 都无所谓):

+---------+-----------------+
| attrCnt | prodOptionCombo |
+---------+-----------------+
|       1 | L - Blue        |
|       2 | M - Blue        |
|       3 | S - Blue        |
|       1 | S - Yellow      |
+---------+-----------------+

包含此信息的表是 store_orders_products_attributes:

products_options 列可以包含任何分类文本分组(自由文本)，例如 Select Flavour、Select xyz 等，因此它不会总是 Colour/Size

也不总是每个产品有两个选项，它可以是 0、1 或 12 或更多。

+-------------------------------+-----------+--------------------+------------------+-------------------------+
| orders_products_attributes_id | orders_id | orders_products_id | products_options | products_options_values |
+-------------------------------+-----------+--------------------+------------------+-------------------------+
|                          1420 |       596 |               2626 | Select Colour    | Blue                    |
|                          1421 |       596 |               2626 | Select Size      | M                       |
|                          1438 |       600 |               2656 | Select Colour    | Blue                    |
|                          1439 |       600 |               2656 | Select Size      | M                       |
|                          1445 |       601 |               2668 | Select Colour    | Blue                    |
|                          1446 |       601 |               2668 | Select Size      | S                       |
|                          1447 |       602 |               2671 | Select Colour    | Yellow                  |
|                          1448 |       602 |               2671 | Select Size      | S                       |
|                          1464 |       611 |               2705 | Select Colour    | Blue                    |
|                          1465 |       611 |               2705 | Select Size      | S                       |
|                          1502 |       634 |               2791 | Select Colour    | Blue                    |
|                          1503 |       634 |               2791 | Select Size      | L                       |
    +-------------------------------+-----------+--------------------+------------------+-------------------------+

store_orders_products 表包含:

+--------------------+-----------+-------------+----------------+---------------+-------------------+
| orders_products_id | orders_id | products_id | products_model | products_name | products_quantity |
+--------------------+-----------+-------------+----------------+---------------+-------------------+
|               2626 |       596 |       88888 | Code123        | Gloves        |                 1 |
|               2656 |       600 |       88888 | Code123        | Gloves        |                 1 |
|               2668 |       601 |       88888 | Code123        | Gloves        |                 1 |
|               2671 |       602 |       88888 | Code123        | Gloves        |                 1 |
|               2705 |       611 |       88888 | Code123        | Gloves        |                 2 |
|               2791 |       634 |       88888 | Code123        | Gloves        |                 1 |
+--------------------+-----------+-------------+----------------+---------------+-------------------+

有什么想法吗？

Answer 1

首先，使用这个子查询而不是原始表:

(
select a1.orders_id,
       a1.products_options_values as sizeval, 
       a2.products_options_values as colourval
from store_orders_products_attributes a1
inner join store_orders_products_attributes a2
  on a1.orders_id = a2.orders_id
where a1.products_options = 'Size'
and a2.products_options = 'Colour' --  Yay, another brit
) x1