mysql - 店铺交易中销量最高的产品

Question

我正在尝试构建一个查询，以在给定要获取的热门产品数量和语言代码的情况下选择最畅销的产品。我不知道该怎么做，所以非常感谢你的帮助。

shop_transactions
id
1
2
3

shop_transaction_products_match
id | shop_transaction_id | product_id | units_bought
1    1                     1            4
2    2                     2            1
3    3                     2            2


products_translations
id | product_id | language_code | name | seo_name
1    1            es              Hola   hola
2    1            en              Hey u  hey-u
3    2            es              Adiós  adios
4    2            en              Bye u  bye-u

products
id | category_id
1    1
2    2

product_categories
id
1
2

product_categories_translations
id | category_id | language_code | name | seo_name
1    1             es              AA     aa
2    1             en              BB     bb
3    2             es              CC     cc
4    3             en              DD     dd

每行的信息是:name from product_translations, seo_name from product_translations, name from product_categories_translations, seo_name from product_categories_translations

按总售出产品数量对后代排序的行。

考虑到示例信息，如果要获取的产品数量为 2 且语言为 es，结果将是:

Hola, hola, AA, aa -> 该产品售出 4 件

Adiós, adios, CC, cc -> 该产品售出 3 件

谢谢!

编辑:

到目前为止我尝试过的代码......我遗漏了一些我不知道如何去做的事情:

SELECT pt.name, pt.seo_name, pct.name as name_category, pct.seo_name as seo_name_category
                                  From product_translations pt, products p, product_categories pc, product_categories_translations pct
                                  where p.id in (Select product_id from shop_transaction_product_match where shop_transaction_id in
                                  (Select id from shop_transactions)) AND pt.language_code = :language_code AND pct.language_code = :language_code
                                  AND p.category_id = pct.category_id

Answer 1

试试这个:

SELECT DISTINCT
 pt.name, pt.seo_name, pct.name, pct.seo_name,
 SUM(stpm.units_bought) units_sold
FROM products AS p
 LEFT JOIN product_categories AS pc ON p.category_id = pc.id
 LEFT JOIN products_translations AS pt ON p.id = pt.product_id AND pt.language_code = 'es'
 LEFT JOIN product_categories_translations AS pct ON pc.id = pct.category_id AND pct.language_code = 'es'
 LEFT JOIN shop_transaction_products_match AS stpm ON p.id = stpm.product_id
-- Where p.id = 1
GROUP BY p.id
ORDER BY units_sold DESC
limit 2