从多个表对MYSQL结果排序的方法是什么？

我有两张桌子.首先：

“ store_products”表：

+----+-----------+
| id | name      | 
+----+-----------+
|  1 | Product 1 |
|  2 | Product 2 |
|  3 | Product 3 |
+----+-----------+

我在这里放置了产品名称.其他表格包含不同产品型号的价格：

“ store_products_variants”表：

+-----+------------+-------------+-------------+
| id  | product_id | price_sale  | ordering    |
+-----+------------+-------------+-------------+
|  5  | 1          |  06.00      | 2           |
|  6  | 1          |  32.00      | 3           |
|  11 | 1          |  56.00      | 1           |
|  14 | 2          |  09.00      | 1           |
|  44 | 3          |  15.00      | 1           |
+-----+------------+-------------+-------------+

我需要创建一个价格排序(最低和最高),该排序仅使用第一个变体-按“ store_products_variants”表中的“ ordering”列进行排序.

从上面的示例中,结果应为：

+---+------------+---------------+
| 1 | Product 2  | (price 09.00) |
| 3 | Product 3  | (price 15.00) |
| 2 | Product 1  | (price 56.00) |
+---+------------+---------------+

这在MySQL中可能吗？

解决方法:

使用订购列将正确的变体连接到产品上.

如果正确的订购值始终为1,则将是该查询.

SELECT 
    products.name,
    variants.price_sale
FROM store_products AS products
INNER JOIN store_products_variants AS variants
    ON variants.product_id = products.id 
    AND variants.ordering = 1
ORDER BY variants.price_sale ASC

该查询将首先查找产品的最低订购值.然后使用它来结合您的结果价格：

SELECT
    products.name,
    variants.price_sale
FROM
    store_products AS products
INNER JOIN (
    SELECT
        product_id,
        MIN(ordering) AS ordering
    FROM
        store_products_variants
    GROUP BY
        product_id
) AS variantOrdering
    ON variantOrdering.product_id = products.id
INNER JOIN store_products_variants AS variants 
    ON variants.product_id = variantOrdering.product_id
    AND variants.ordering = variantOrdering.ordering
ORDER BY 
    variants.price_sale ASC